Is a MySQL DATETIME or TIMESTAMP value retrieved through $wpdb in UTC?

by

I have a custom MySQL table with two columns that look like this:

`timestamp_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`datetime_created`  DATETIME DEFAULT CURRENT_TIMESTAMP,

When retrieving these values using $wpdb, I would like to know in what timezone the result will be, and whether I can have the result be in UTC:

$row = $wpdb->get_row("SELECT * FROM `test`", ARRAY_A);
var_export( $row );

Background knowledge:

I know that WordPress runs this line with every request, regardless of the configuration of PHP or MySQL or the server, which affects built-in PHP functions:

date_default_timezone_set( 'UTC' );

I know that administrators can select a timezone in the admin interface, which get stored in the database, and can be retrieved using get_option( 'timezone_string' ).

I know that the server and MySQL itself might not be configured to use UTC. Here is a query proving that UTC is not used here:

mysql> select now(), utc_timestamp();
+---------------------+---------------------+
| now()               | utc_timestamp()     |
+---------------------+---------------------+
| 2019-05-30 19:26:04 | 2019-05-30 18:26:04 |
+---------------------+---------------------+

1 Answer
1

Summary:

WordPress does run date_default_timezone_set( 'UTC' );. It does have a timezone option modifiable by the admins and retrieved using get_option( 'timezone_string' ). However, none of that has any effect on the query run through $wpdb. You should get the same results from $wpdb as if you had run the MySQL query without WordPress.

Details on how MySQL works:

This doesn’t necessarily mean that the value returned will be in UTC, however. That depends on the configuration of the server and of MySQL, and whether you used TIMESTAMP or DATETIME. As long as you never change the configuration of the server, you shouldn’t notice any differences (in terms of timezones) between TIMESTAMP or DATETIME.

mysql> SELECT `timestamp_created`, `datetime_created` FROM `test`;
+---------------------+---------------------+
| timestamp_created   | datetime_created    |
+---------------------+---------------------+
| 2019-05-30 20:31:04 | 2019-05-30 20:31:04 |
+---------------------+---------------------+

As long as you never change the configuration of the server, you can get the number of seconds since the UNIX epoch in UTC like this, both will give the same correct result:

SELECT UNIX_TIMESTAMP(`timestamp_created`) unix_ts, UNIX_TIMESTAMP(`datetime_created`) unix_dt FROM `test`;
+------------+------------+
| unix_ts    | unix_dt    |
+------------+------------+
| 1559244664 | 1559244664 |
+------------+------------+

As soon as the timezone configuration of MySQL or the server does change, you will notice a difference between TIMESTAMP and DATETIME:

mysql> SET time_zone="-8:00";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `timestamp_created`, `datetime_created` FROM `test`;
+---------------------+---------------------+
| timestamp_created   | datetime_created    |
+---------------------+---------------------+
| 2019-05-30 11:31:04 | 2019-05-30 20:31:04 |
+---------------------+---------------------+

You can see that even though we ran it on the same table with the same values as the earlier query, we got a different result in the first column, but unexpectedly the same result as earlier in the second column. This is because TIMESTAMP (the first column) recognises the change in the configured timezone, and is converting to the new timezone on display. Behind the scenes, TIMESTAMP is stored as the number of seconds since the Unix Epoch in UTC. However, DATETIME just stores the date time naively as is, without reference to any timezone information, so it’s giving us the same result, even though we changed the timezone in MySQL.

UNIX_TIMESTAMP knows how to behave correctly with TIMESTAMP values. With DATETIME values, it makes the assumption that the value is in the timezone of the current configuration, which in this case is a false assumption:

mysql> SELECT UNIX_TIMESTAMP(`timestamp_created`) unix_ts, UNIX_TIMESTAMP(`datetime_created`) unix_dt FROM `test`;
+------------+------------+
| unix_ts    | unix_dt    |
+------------+------------+
| 1559244664 | 1559277064 |
+------------+------------+

Recommendation:

If you want to use DEFAULT CURRENT_TIMESTAMP, use the TIMESTAMP type not the DATETIME type if you care about timezone correctness. When retrieving the value, use UNIX_TIMESTAMP(`timestamp`), your code can easily convert that to a human-readable UTC date-time string.

Leave a Comment