Can PHP safely use unix timestamps to query mysql database?

Question

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?

Answer 1

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.

Answer 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.