NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ
for more details.
For my travelblog.ws
project I store all dates as UTC
in columns using the DATETIME
data type i.e. my code converts dates to UTC
before storing them. Because DATETIME
is time zone agnostic this worked great, that is until I needed to use the TIMESTAMP
data type on a number of new columns that I've added.
data type is treated differently by MySQL
, with it's values being converted to UTC
for storage and back to the system time zone
upon retrieval. This messed up a number of dates for me because all of a sudden some dates that should have matched were now 10 hours out (I'm in the AEST/GMT+10
The solution was to force MySQL
to think it's in the UTC
time zone so there was no need to worry about these automatic conversions. The catch was that I was not able to change the time zone setting on the MySQL
server itself, so the change had to be done on a connection basis.
I was using PDO
and simply executing "SET time_zone = '+00:00'"
didn't seem to work for me, but eventually I did find a way to do this. Below is the code I used (adjust connection details to suit)...
The trick was to set the PDO::MYSQL_ATTR_INIT_COMMAND
value to the SET time_zone = '+00:00'
statement when creating the PDO
Once I added that code, MySQL
treated all of my connections as if they were in the UTC
Hope you found this post useful...
...so please read on! I love writing articles that provide beneficial information,
tips and examples to my readers. All information on my blog is provided free of
charge and I encourage you to share it as you wish. There is a small favour I ask in return however -
engage in comments below, provide feedback, and if you see mistakes let me know.
If you want to show additional support and help me pay for web hosting and
domain name registration,
donations, no matter how small, are always welcome!
Use of any information contained in this blog post/article is subject to this disclaimer
Other posts you may like...