Working with Dates and Times

cadkins23

Ok I am planning on creating a new feature for my site, an Event Planner.

Basically the concept is users can add an event and specify starting and ending dates and times. For storage purposes and so that I can work with the date and times (not display events that have ended, etc.) what would be the best way to do this? Perhaps just use the date and custom field to store the times since I really won't need to search by time?? hmmm

I know MySQL has date/time but is that easy to work with? Would I be better using the date function and perhaps separately create a simple INT type row to store the times such as 00 for midnight 01, etc... I'm sure I can figure something out but it's nice knowing you guys know more than me and can tell me what is the best way so I can save some headaches and some time.

Use the date/time data type or something else? Really the only thing I'll be doing with the dates is to allow people to search for events during certain time periods that they specify and of course I don't want to show events on my front page that have already ended.

Thanks!

Chris

blackhorse

I will save it in unix timestamp.

And when you transfer unix timestamp to date format or vice versa, use the http://www.php.net/manual/en/function.date-default-timezone-set.php to set up the timezone right.

Weedpacket

I'd recommend storing the dates and times as dates and times, using the UTC timezone (aka GMT). Then you'd be able to work with MySQL's date and time functions when searching.

cadkins23

Thanks guys.. Weedpacket, "Foreign Object Damage" reminds me of when I was an F16 crewchief in the Air Force. 🙂 We would go on FOD walks every day before launching our jets.

Any way you're saying to use MySQL's date/time data type and when actually storing the data to convert it to UTC (GMT)? Makes sense!

Chris

Weedpacket

Well, that's what I use; the primary advantages are that it isn't affected by any timezone changes; and since it's independent of the server's local timezone (assuming that's correct then it's easy to convert to UTC) - so the system can be easily transferred to a host in another timezone without worry about having to convert anything. Another advantage is that if you want to display local times in a different timezone from yours then you only have to make one conversion, not two.

Unix timestamps do have the same advantages, it's true; but to do any date/time related calculations (like selecting records for the past seven days) you'll need to convert it before you could use any of the database's date and time functions; and if you're going to do that every time, it seems better to just do it once and store the result. Also (possibly, I don't know) it might be that the scheme MySQL uses to index dates and times is optimised for such searches.

And while I've never been in the Air Force, I used to have a holiday job during race season which included fodding the drag strip.