It can be very challenging to work with dates and times across different timezones. In this post I explain some of the things I do to make this work. I focus on Javascript, PHP, MySQL and SQLite development.

MySQL

The most important action first - put the connection session in UTC mode:

set time_zone = '+00:00';

After this is done, both NOW() and UTC_TIMESTAMP() return the same value. The FROM_UNIXTIME() call returns UTC instead of local datetime and UNIX_TIMESTAMP() returns the seconds since 1970-01-01 UTC (called Unix epoch).

PHP

To set the default timezone:

date_default_timezone_set('UTC');

To get a DateTime object from a Unix timestamp:

$date = new DateTime('@' . $seconds);

In the above constructor, the Unix timestamp is always interpreted as UTC time, even if you specify a timezone as the second argument.

To get back some nice format:

echo $date->format('Y-m-d H:i:s');

SQLite

SQLite is by default in UTC. So both datetime('now') and datetime([seconds], 'unixepoch') return a UTC datetime.

Javascript

To get the milliseconds since Unix epoch:

Date.now();

Or:

var date = new Date();
date.getTime();

Both methods return milliseconds and not seconds!

When you use the constructor that accepts milliseconds, it is always interpreted as UTC time:

var date = new Date(ts);

When you call the constructor that accepts year, month, etc. it is always interpreted as local time:

var date = new Date(2014, 9, 8, 14, 30, 0);

If you want to convert some UTC datetime to a Date object, you can use the Date.UTC() method:

var date = new Date(Date.UTC(2014, 9, 8, 14, 30, 0));