php mysql UTC timestamp

Question

I'm in the early stage of building a mysql table that will hold data from two timezones. Both my machine and the server are in the America/Los_Angeles time zone. The timezone table is not loaded in the mysql server, but elsewhere on this site, I've read this is not necessary as long as php handles the queries - php will write the UTC offset to mysql. Mysql datatype is TIMESTAMP. Sample data has been inserted with a php script that includes the following statement:

        if($company == 'HOS_CIN' or $company == 'HOS_FER') {
        date_default_timezone_set("America/New_York");
    }

Then two php scripts were used to display the data in a browser. One included the above statement and one did not. The one with the statement displayed the time as noon EST, and the one without displayed the time as noon PST. If mysql had stored the UTC offset, shouldn't there have been a three-hour difference in the times displayed?

Php version 5.3.3, mysql version 5.0.95

1) No, PHP will not print a UTC time zone if you don't tell it to (e.g., setting time zone to UTC) 2) TIMESTAMP converts from/to MySQL Server's time zone when storing/retrieving dates, it doesn't seem you take care of setting a known one. — Álvaro González
– Álvaro González, Commented Dec 27, 2016 at 15:41
Not sure what you mean now, if you pick America/New_York it'll use New York time, not UTC. And MySQL is a different program that some times even runs on a different computer; it won't care about PHP time zone. — Álvaro González
– Álvaro González, Commented Dec 27, 2016 at 15:57
Elsewhere on this site and others, the advice isn't consistent. Some suggest storing timestamps as integers while others suggest that mysql will convert and store date and time data initiated in a php script as UTC when the datatype is set to timestamp. My reasoning in opting for named timezones in php was to anticipate conflicts arising from that abominable daylight savings time. The queries mentioned are issued from a mysqli_query statement in php. — dirk
– dirk, Commented Dec 27, 2016 at 16:37
I'm not stating whether your method is good or bad, it's just that you aren't taking all information into account (e.g. Mysql time zone) and you make assertions (e.g. "php will write the UTC offset to mysql") not supported by code. — Álvaro González
– Álvaro González, Commented Dec 27, 2016 at 16:41

Álvaro González · Accepted Answer · 2016-12-27 17:43:27Z

You have to face the following limits regarding PHP and MySQL:

MySQL does not have any column type that allows to store a local time with time zone information.
PHP cannot pass complex data types (such as DateTime instances) to MySQL, everything needs to be stringified, and MySQL doesn't have a syntax to pass a date literal with time zone information.

In practice it isn't as bad as it may seem because you don't normally need the local time of the user who inserted the information: you just need to know the exact moment in time the stored date refers to and (optionally) the time zone in which the stored date has to be displayed. And there're basically three sensible ways to do so:

Use a format that's unaffected by time zones, e.g. a Unix timestamp stored as INT.
Use a date column type with explicit time zone information, e.g. a TIMESTAMP column (not to be confused with Unix timestamps) where time zone is always UTC.
Use a date column type with implicit time zone information, e.g. a DATE or DATETIME column where you have decided that all dates belong to a given time zone, possibly UTC.

The difference between #2 and #3 is whether MySQL is aware of the time zone or it's something only you and your code know.

Whatever approach you chose, it's necessary that all the programs that are expected to do time zone conversions are instructed about what time zone to use:

PHP needs the time zone to generate/display Unix timestamps and to convert from/to the local time typed/expected by user and the one expected/printed by MySQL, e.g.:

$user_date = new DateTime('2016-10-03 00:00:00', new DateTimeZone('Europe/Berlin'));
$user_date->setTimezone(new DateTimeZone('UTC'));
echo $user_date->format('c');

MySQL needs the time zone when you use TIMESTAMP columns, to convert from/to the local time, e.g.:

mysql> create table foo(
    -> foo int(10) unsigned auto_increment,
    -> my_date timestamp,
    -> primary key (foo)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> set @@time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (my_date) values ('2016-12-27 18:50:00');
Query OK, 1 row affected (0.00 sec)

mysql> set @@time_zone = '-07:00';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (my_date) values ('2016-12-27 18:50:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo order by 1;
+-----+---------------------+
| foo | my_date             |
+-----+---------------------+
|   1 | 2016-12-27 10:50:00 |
|   2 | 2016-12-27 18:50:00 |
+-----+---------------------+
2 rows in set (0.00 sec)

mysql> set @@time_zone = '+09:30';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo order by 1;
+-----+---------------------+
| foo | my_date             |
+-----+---------------------+
|   1 | 2016-12-28 03:20:00 |
|   2 | 2016-12-28 11:20:00 |
+-----+---------------------+
2 rows in set (0.00 sec)

Collectives™ on Stack Overflow

php mysql UTC timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related