Time zone conversion
from the Artful MySQL Tips List
Here is a workaround for importing time data from servers that do not observe daylight saving time (DST), to a server that does.
1. Ensure MySQL time zone tables are loaded.
2. Collect the source timestamp with gmmktime(), offset by the local number of hours from UTC:
$timestamp = gmmktime($hours + $localHrsFromUTC, $minutes, $seconds, $month, $day, $year, $isDaylight);
3. Retrieve with:
SELECT CONVERT_TZ(FROM_UNIXTIME(timestamp-28800), 'LOCAL TIMEZONE NAME','UTC')
FROM tableName;
Without CONVERT_TZ
, FROM_UNIXTIME()
converts the UTC timestamp to the server's timezone, which ignores DST.
Return to the Artful MySQL Tips page