I've searched and read some other posts regarding MySQL timestamp/datetime conversions, but I believe my problem here is not answered in those. It is quite specific.
I have some Javascript extracting a Program Date Time (PDT) value, first_pdt, from a livestream and posting to PHP via AJAX:
$first_pdt = isset($_POST['first_pdt']) ? $_POST['first_pdt'] : NULL;
$first_pdt should have an ISO 8601 format, probably with fractional seconds like so:
2021-09-21T22:19:51.689+00:00
Note a) the fractional seconds and b) the timezone offset.
I need to a) store this in a db such that no time conversions occur -- it's a timestamp -- and b) periodically compare the db stored value to a new, incoming value from a subsequent POST. This is deceptively difficult.
For starters, strtotime() ignores the fractional seconds:
$ php -r 'var_dump(strtotime("2021-09-21T22:19:51.689+00:00"));'
int(1632262791)
$ php -r 'var_dump(strtotime("2021-09-21 15:19:52"));'
int(1632262792)
So, I have bothered to cook up a bit of regex and round code to round it to this before any db inserts or comparisons:
2021-09-21T22:19:52+00:00
Next, I want to insert this value into the database unmolested by any timezone conversions. The MySQL docs say that:
In MySQL 8.0.19 and later, you can specify a time zone offset when inserting a TIMESTAMP or DATETIME value into a table.
This strongly suggests to me that supplying the time zone offset will short-circuit any conversion attempts and telegraph to MySQL that I'm talking about the time in some specific time zone. I tried a bit of experimentation. I've defined this table on my workstation (PDT time) and a server (UTC time), each of which is running MySQL server:
mysql> explain wp_video_tracking;
+--------------+------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+-------------------+-------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| created | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| last_updated | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| user_id | bigint | NO | MUL | NULL | |
| video_id | bigint | NO | MUL | NULL | |
| watch_data | text | NO | | NULL | |
| pdt_method | varchar(5) | YES | | NULL | |
| first_pdt | datetime | YES | | NULL | |
And I've inserted a record on both machines:
INSERT INTO wp_video_tracking (user_id, video_id, watch_data, pdt_method, first_pdt) VALUES (3, 3, 'foo', 'bar', '2021-09-21T22:19:52+00:00');
And then I've connected to each machine via SSH, opened a mysql client session via CLI, and queried for the new record. My workstation (PDT time):
mysql> SELECT * FROM wp_video_tracking WHERE user_id=3 AND video_id=3;
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
| id | created | last_updated | user_id | video_id | watch_data | pdt_method | first_pdt |
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
| 8 | 2021-09-30 10:29:40 | 2021-09-30 10:29:40 | 3 | 3 | foo | bar | 2021-09-21 15:19:52 |
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
and the server (UTC time):
mysql> SELECT * FROM wp_video_tracking WHERE user_id=3 AND video_id=3;
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
| id | created | last_updated | user_id | video_id | watch_data | pdt_method | first_pdt |
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
| 2 | 2021-09-30 17:30:50 | 2021-09-30 17:30:50 | 3 | 3 | foo | bar | 2021-09-21 22:19:52 |
+----+---------------------+---------------------+---------+----------+------------+------------+---------------------+
You'll note the first_pdt values differ by the difference between the two timezones. Unless I'm missing something, these converted values appear to contradict the MySQL documentation which says:
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
In any case, the select results seem encouraging inasmuch as I think both mysql databases have captured the exact same UTC timestamp for first_pdt, just interpreted differently because of the different timezones. This query returns the same exact value on both machines:
mysql> SELECT UNIX_TIMESTAMP(first_pdt) FROM wp_video_tracking WHERE user_id=3 AND video_id=3;
+---------------------------+
| UNIX_TIMESTAMP(first_pdt) |
+---------------------------+
| 1632262792 |
+---------------------------+
It's discouraging, though, because when I fetch this first_pdt value, the machine's time zone results in a conversion being applied. I suspect that the actual db engine stores an int or something, and the SELECT behavior of the mysql client -- the closest I could get to the actual db -- is to convert this to a string for User Friendliness™.
It is with growing horror that I realize that the conversion might depend on any number of settings:
- Ubuntu system time
- PHP.ini default time zone
- Wordpress timezone setting
- WP timezone either specified when db connections is made (Denver!) or NOT applied (UTC?)
And still more horror when I realize these values might have some TZ conversion applied when I do any of the following:
- change mysql timezone setting
- mysqldump the data and reload into a future mysql server
- daylight savings time?
For my current application, if I stick a $first_pdt of 2021-09-21T22:19:52+00:00 into the db, I'd like to see that value returned when I retrieve the record, but there doesn't appear to be any time zone data stored in each db record, and, unlike the PHP date() function, the MySQL DATE_FORMAT function doesn't have any time zone specifiers . Unless I' missing something, one would have to cook up some kind of fancy CONCAT query to get the correct timezone offset and this seems like it would entail a time zone conversion. I'm considering storing it as varchar to avoid any conversions at all, but that precludes all the useful date range functions and indexing I might want to use for sorting/limiting/formatting this column.
In PHP, I need to merge an incoming first_pdt value and its associated data array against the previously stored first_pdt and its data array and merge the two arrays -- the first_pdt values are offsets for each data payload and must be compared to align each element of the data payload. That being the case, I'll need to convert both POST and DB first_pdt values to integers for this alignment & merge. As you can imagine, any rounding errors could cause an off-by-one drift as records are repeatedly updated from incoming POST and timezone corrections could introduce far more extreme alignment problems with the data payloads.
I'm gradually starting to think I might be able to avoid conversions on storage because of the extra time zone offset and then avoid conversions on SELECT by casting or by using UNIX_TIMESTAMP. Any other approach like CONVERT_TZ or other time zone specific conversion seems like it might break because of the many settings that might affect this. I'd like my code to work regardless of any time zone settings on client, server, PHP, or mysql.
Can anyone suggest a simple approach to storing a UTC timestamp which provides the convenient formatting and ordinal treatment of mysql timestamp/datetime fields but which avoids unwanted conversion?
