Central repository for time zone information

timetimezone

I know that systems are using their own time zone databases at different levels.

Is there a central place where all timezeone and DST information lives, that makes it easy for programmers to write updates to their time zone data sources?

For example, do people make provisions for a time zone for each possible combination? For example, islands in Mexican territory don't follow DST, so they would each have to be different from the three mainland time zones (that should be described w/ DST date ranges).

Best Answer

The best database I know of is the tz database, also known as the Olson database.

There are three places where the timezone might be set in MySQL:

In the file "my.cnf" in the [mysqld] section

default-time-zone='+00:00'

@@global.time_zone variable

To see what value they are set to:

SELECT @@global.time_zone;

To set a value for it use either one:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone = '+00:00';

(Using named timezones like 'Europe/Helsinki' means that you have to have a timezone table properly populated.)

Keep in mind that +02:00 is an offset. Europe/Berlin is a timezone (that has two offsets) and CEST is a clock time that corresponds to a specific offset.

@@session.time_zone variable

SELECT @@session.time_zone;

To set it use either one:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

Both might return SYSTEM which means that they use the timezone set in my.cnf.

For timezone names to work, you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html. I also mention how to populate those tables in this answer.

To get the current timezone offset as `TIME`

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return 02:00:00 if your timezone is +2:00.

To get the current UNIX timestamp:

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());

To get the timestamp column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

To get a UTC datetime column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`

Note: Changing the timezone will not change the stored datetime or timestamp, but it will show a different datetime for existing timestamp columns as they are internally stored as UTC timestamps and externally displayed in the current MySQL timezone.

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

Javascript – Getting the client’s time zone (and offset) in JavaScript

Using an offset to calculate Timezone is a wrong approach, and you will always encounter problems. Time zones and daylight saving rules may change on several occasions during a year, and It's difficult to keep up with changes.

To get the system's IANA timezone in JavaScript, you should use

console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)

As of September 2019, this works in 95% of the browsers used globally.

Old compatibility information

ecma-402/1.0 says that timeZone may be undefined if not provided to constructor. However, future draft (3.0) fixed that issue by changing to system default timezone.

In this version of the ECMAScript Internationalization API, the timeZone property will remain undefined if no timeZone property was provided in the options object provided to the Intl.DateTimeFormat constructor. However, applications should not rely on this, as future versions may return a String value identifying the host environment’s current time zone instead.

in ecma-402/3.0 which is still in a draft it changed to

In this version of the ECMAScript 2015 Internationalization API, the timeZone property will be the name of the default time zone if no timeZone property was provided in the options object provided to the Intl.DateTimeFormat constructor. The previous version left the timeZone property undefined in this case.