mysql time data problems

[neon Source]

I have a db of events each with a start time and end time and i am using it to select the event based on the current time by simply selecting the entry that has start time less than the current time and an end time more than the current time. The problems start when an event spans midnight and goes into the next day. MySql doesn't recognise that say 23.30 is inbetween 22:00 and 01:00 the next day. Any recommedations how to fix this? I'm pretty stuck

[Yesideez]

Change the field type to DATETIME.

 

By the sounds of it you're using a field of just DATE type.

[neon Source]

There is no actual date info just time and a numerical day of the week

[PFMaBiSmAd]

You must store a DATETIME value. There is no way for a computer to determine what day a time belongs with unless you tell it. Even with a day of the week, your system will fail to work if an event is longer than one week. Use a DATETIME to make it general purpose so that it will work for all possible values (this will result in the simplest code as well.)

[Yesideez]

There is no actual date info just time and a numerical day of the week

Sorry I actually meant to type "TIME" and not "DATE".

[neon Source]

You must store a DATETIME value. There is no way for a computer to determine what day a time belongs with unless you tell it. Even with a day of the week, your system will fail to work if an event is longer than one week. Use a DATETIME to make it general purpose so that it will work for all possible values (this will result in the simplest code as well.)

 

As i said there is no date info. The events are not date specific only time and day of the week. So a DATETIME datatype will not work as it requires a date and there simply isnt one. There's no potential for any event to last longer than 6 hours i think.

[revraz]

The problem you are facing is that they are specific dates because you are crossing into a new day when the time goes past midnight.

 

You would have to use code logic instead of mysql logic if you want to keep it they way you have it.

[neon Source]

The problem you are facing is that they are specific dates because you are crossing into a new day when the time goes past midnight.

 

You would have to use code logic instead of mysql logic if you want to keep it they way you have it.

 

specific days, not dates. It's a week cycle rather than linear time.

[PFMaBiSmAd]

If you provide a specific example of what your data is, what it means, and what the expected results should be, someone can probably help you. As it is, you have not provided any directly useful information for someone to help you with.

[neon Source]

If you provide a specific example of what your data is, what it means, and what the expected results should be, someone can probably help you. As it is, you have not provided any directly useful information for someone to help you with.

 

Apologies, i thought i was clear. The data is a schedule of programs that repeats exactly the same every week indefinitely. The Table is simple, it has fields for the title, program description, numerical day of the week, start time and end time which are both TIME datatypes

 

I could, as suggested, input dates but that would require repeating the data for every week and it could never be indefinite and would serious enlarge the database size and database optimization is very much about avoiding such repetition.

 

I figured there would be an obvious way to do this as humans typical perceive time in cycles rather than linear as a computer does.

[PFMaBiSmAd]

That explains your data, now what are you trying to do with it? Display events that are active for a specific day/time? Display events between two times? Display events on a specific day? Display all upcoming events in a weekly calendar? Add duration of events for some range? Find open event slots? Showing a specific example with the expected results is going to get you a solution a lot faster.

[PFMaBiSmAd]

In the end, you are going to need to produce the corresponding DATETIME values that correspond to the day of week/start time and day of week/end times before you can compare with the current time.

[neon Source]

In the end, you are going to need to produce the corresponding DATETIME values that correspond to the day of week/start time and day of week/end times before you can compare with the current time.

 

That's an idea to try. Making the unspecified week the current week when accessed. It would require extra coding to update the db every week but would allow the databse to stay small.