Staff mentioned in the forum that the MySQL time zone is set to ‘EST (UTC-5)’.
However, when I ran the query SHOW VARIABLES LIKE '%time_zone%' in phpMyAdmin, the system_time_zone value was ‘PDT’.
To test this, I created a table: CREATE TABLE test (created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP);
After adding a record and retrieving it via PHP, I used the following simple conversion function: function convert_tz($time, $from_tz, $to_tz) { $date = new DateTime($time, new DateTimeZone($from_tz)); $date->setTimezone(new DateTimeZone($to_tz)); return $date->format('Y-m-d H:i:s'); }
Then I executed: $est = convert_tz($dt, "EST", "JST"); $pdt = convert_tz($dt, "PDT", "JST");
(I reside in Japan.)
The result for $est was off by two hours, whereas $pdt yielded the correct Japan time.
Does this imply that I need to account for Daylight Saving Time?
Could you please provide official information regarding the current MySQL time zone settings?
Honestly I’ve personally found rather than messing with that, you’re better storing the time as a strong and converting it as need in your php after the fact.
Then you can just set your php settings in the dashboard
Storing the data as a string would certainly make the logic easier to handle. However, since sorting by date is a crucial requirement, I would prefer to store it using a DATETIME type (or similar) to ensure better search performance.
Hmm, you’re right on the timezone, it seems that the database servers are now in UTC-7, not UTC-5. Strange. I don’t know when or why it was changed, and I would not have chosen this setting myself.
Still, while the exact timezone was wrong, the other advice I give in such cases still applies: do not use MySQL to get the current time. Always generate times and dates from PHP, where you know you can configure the time zone.
MySQL does not store times with timezone information, and it uses the timezone of the server it runs on.
So while your “convert on retrieval” approach works fine now, it will cause a big headache if you ever migrate to a server with a different timezone since any new timestamps created on that server will have that server’s timezone.
Using DATETIME and TIMESTAMP data types is perfectly safe, and storing dates as strings is a waste of storage space in my opinion.
So I recommend to remove the DEFAULT CURRENT_TIMESTAMP from your table, and have PHP provide the time instead. Configure the PHP timezone to JST, and you’ll always be sure that your timestamps in your code and your database align, even if you ever migrate to a different hosting server.