2

There's lots of recommendations out there about handling dates. I'd just like to clarify something. Let's say:

  • user is inserting records into a database
  • unix timestamps of insertion date are generated for this record

Now the user wants to query a date interval in the database:

  • user provides 2 dates in his local timezone
  • use these values to convert timezone to UTC and get the timestamp
  • query the records in the database based on the 2 integers from conversion (eg. WHERE date >= FIRST and date <= SECOND)
  • convert the retrieved timestamps to local timezone again for display

I know that would be possible with PHP, but and wouldn't need to care about mysql's timezone settings in this case - only php's. The system would be 64 bit so running out of space to store the date is not an issue. But ...

Would that raise any other serious issues like with DST changes or something else?

Dmytro
  • 16,668
  • 27
  • 80
  • 130
Marius
  • 3,976
  • 5
  • 37
  • 52
  • What makes you suspect these issues? Because you're not sure how Mysql handles these timestamps, e.g. to which timezone the ones in the database belongs to? And are you storing the dates as Unix Timestamps or how do you store them? – hakre Sep 21 '12 at 11:18
  • I'm suspicious by nature. Since i don't know everything about date and time handling, it's only natural for me to seek answers just to make sure everything is ok. And no, i'm storing unix timestamps as big integers instead of TIMESTAMP field. I don't want mysql to even know we're dealing with dates here. – Marius Sep 21 '12 at 12:41
  • Then see @Tadeck's answer I'd say. – hakre Sep 21 '12 at 12:41
  • Never mind that - just remembered that mysql based date functions will have to be used so i will have to store the timestamp as a TIMESTAMP. – Marius Sep 21 '12 at 12:49

2 Answers2

2

Unix timestamp is timezone-independent.

This is also the reason you can change this step:

use these values to convert timezone to UTC and get the timestamp

into this:

convert values to Unix timestamp

Although storing timestamps in the database (eg. MySQL) is very simple. You can make sure PHP has Unix timestamp, if you will:

  • save the values by using FROM_UNIXTIME() MySQL's function (give Unix timestamp as argument and you will receive datetime according to MySQL's settings),
  • retrieve the values by using UNIX_TIMESTAMP() MySQL's function (give the name of the field, or the value, as the argument), so you will get Unix timestamp (integer) on the basis of datetime stored in the database according to MySQL's settings.

Just remember to use TIMESTAMP column type to store timestamps. This way the time will be stored in timezone-independent manner, only displayed according to MySQL's settings.

Tadeck
  • 132,510
  • 28
  • 152
  • 198
  • You're right about mysql's version, but ... I want to be able to do things independently from mysql. Would (in your case) i be able to work safely independently of mysql's timezone setting? – Marius Sep 21 '12 at 11:57
  • @Marius: I am just saying `TIMESTAMP` is very similar to `INT` containing Unix timestamp and does not have issue with timezones. See couple comparisons (this is really a subject for the whole new discussion): http://stackoverflow.com/a/7029306/548696 and http://stackoverflow.com/a/3315655/548696. When it comes to doing things independently from MySQL, then some abstraction layer may include transformations between PHP and MySQL (like in various ORMs), you won't hide from MySQL completely. Also with `TIMESTAMP` you are able to use DATETIME-related functions, groupings etc. – Tadeck Sep 21 '12 at 12:03
  • Thanks for explaining. I thought i could do without mysql. That is ... until you reminded me about grouping :) I guess i'll have to store TIMESTAMP. – Marius Sep 21 '12 at 12:47
0

Tadeck is correct that Unix timestamps are timezone-independent.

But when using timestamps throughout your application you should store and use timestamps in the database as plain INTs. Convert to and from local timezones at the application level (in PHP). That allows you to only concern yourself with timezones in PHP and not in 2 systems. It also eases setting time zones for individual users at the application level.

Matt S
  • 14,976
  • 6
  • 57
  • 76
  • Thanks, but the point is that when using `TIMESTAMP` columns, the datetimes are stored also in timezone-independent format and this does not limit you in any way from allowing users to set timezones in their own way. MySQL's `TIMESTAMP` column was designed specifically for that: for storing timestamps. Although there are two different opinions on storing timestamps: some say you should use `INT`s, some say you should use `TIMESTAMP` (the ones saying you should use `DATETIME` are of course incorrect ;)). – Tadeck Sep 21 '12 at 11:43
  • I appreciate that point of view, but timestamps are just INTs. So when comparing in SQL, INT is fine. Any local conversion I prefer to perform at the app level. This way mysql's time zone settings are irrelevant. – Matt S Sep 21 '12 at 11:47
  • I do store them as big ints though. – Marius Sep 21 '12 at 12:00
  • @MattS: I understand these arguments. I also were a fan of storing timestamps as `INT`s until I actually learned that at some point I usually needed interpretation of timestamp on the database layer (eg. I needed to use one of datetime-related SQL functions), and then I really needed `TIMESTAMP` instead of integer. Here is another comment on that: http://stackoverflow.com/questions/12529411/can-php-safely-use-unix-timestamps-to-query-mysql-database#comment16869608_12529465 – Tadeck Sep 21 '12 at 12:05