[RESOLVED] Date best practices?

SundariDevi

I have a reservation program that lets somebody enter a reservation for a specific date. The bookingdate variable is just the moment they make the reservation, but the event date requires them to enter month - day - time from 3 select menus. So I set it up like this.

bookingdate - datetime (because i want the calendar date and the time)

event date - unix timestamp (i convert to timestamp using the three values)

That all works fine, but now i'm adding some reporting and i when i wanted to display reservations by date there are two options: by booking date and by event date

So when they are on the reporting page and they search for the specific dates, they are again entering a start date and end date to search for by selecting the month-day-time. When they search by event date, I just convert to timestamp and compare it to the value in the database and that works fine.

I only run into a problem on the bookingdate because it's in the datetime format. Is there some way to convert the month-day-time values into datetime, so i can search or is it better to store the booking date as timestamp too?

In general, what are the best situations to use all the different date formats?

Weedpacket

...And then use the date and time functions your DBMS provides.

dalecosp

'Cause I just LOVE UNIX Timestamps, and math for large numbers 😉

😃

Weedpacket

dalecosp wrote:
and math for large numbers 😉

Which is why I store all my dates as Julian days.
[Posted: 2456069.68656] 😃

SundariDevi

bradgrafelman;11004763 wrote:
Why not use the DATETIME column type for both columns, since both contain data that represents a date and a time?

The reservation is only a date the time is stored separately and not used for searching or displaying the data.

dalecosp;11004774 wrote:
'Cause I just LOVE UNIX Timestamps, and math for large numbers 😉
😃

Does this mean that timestamps are large?

It seems like nobody likes them?

Are there any advantages or disadvantages of the different functions? For the booking date I definitely need to store the time and date together.

Bonesnap

Sadly I was taught in college to use Unix timestamps. When I discovered the DATETIME data type I asked the professor what it was for and he casually responded, "To store dates and times". I stared at him and then just decided to use the DATETIME format in all future assignments.

SundariDevi

@: the date and time for the reservation are stored separately because the clent asks for a date and the service provider gives them a time for that date, so there is no time to enter and the date has no corresponding time at the time of the reservation, or anywhere in the database. I guess it could in the future :-)

I went back over my development notes on this and came up with: I set it up this way because the datestamp gives the server time, it also tries to compensate for time differences whereas timestamp is just whatever time you give it. Since we have bookings coming in from all over the world. With so many time formats, I thought it would be better to ignore the local time of the guy who is using the site and use datetime because I know exactly what I have. But in the end it won't matter.

I'll see if datetime makes sense otherwise. It will be better to have everything in the same format.

Derokorian

Why not store all the times in as UTC timezone and then simply adjust the time to the region when displaying it?

SundariDevi

I read a bunch of stuff about this and there is really no consensus. This sums it up pretty well though:
http://www.richardlord.net/blog/dates-in-php-and-mysql

But I think timezones could be an issue if you're not careful

SundariDevi

Thanks for the corrections. I decided to use datetime because I liked the argument that said

However, some queries will be more complicated because your date is not in a date field (e.g. select all users who’s birthday is today) and you may lose out in the long run.

dalecosp

I actually do like UNIX timestamps; first because 'Nixen are my preferred OS for programming environments and hosting, and second because of their granularity (I don't have to worry when designing something just how specific I'll need to be regarding dates, since I know with the Nixen TS I'll have it down to the second --- granted, I'm not a programmer for NTP or the Naval Observatory, etc.)

I am a bit worried though, as whether or not anything I write will still be in production in 2038, though. 😃