{"id":287,"date":"2012-05-16T16:55:21","date_gmt":"2012-05-16T15:55:21","guid":{"rendered":"http:\/\/www.cotsweb.com\/blog\/?p=287"},"modified":"2012-05-16T16:55:21","modified_gmt":"2012-05-16T15:55:21","slug":"keeping-php-and-mysql-dates-in-sync","status":"publish","type":"post","link":"https:\/\/www.cotsweb.com\/blog\/keeping-php-and-mysql-dates-in-sync-287.html","title":{"rendered":"Keeping PHP and mySQL dates in sync"},"content":{"rendered":"

My websites are hosted with Hostgator<\/a> in Texas, USA but most of my customers are based in the UK. This creates a potential problem with dates and times because we usually want timestamps etc. to be recorded as if they were in the UK timezone rather than the timezone where the server is.<\/p>\n

Setting the timezone for Apache<\/h3>\n

Fortunately it is easy to tell Apache to use a different timezone, just add the following line to your .htaccess file.<\/p>\n

SetEnv TZ Europe\/London<\/code><\/p>\n

This works nicely to set the timezone for the webserver and the programs that run within it, including PHP. This means that the PHP date and time functions will return the date\/time as UK time rather than the server time.<\/p>\n

Getting mySQL to use the right time<\/h3>\n

The only problem is that Apache doesn’t control mySQL, in fact on a shared hosting arrangement it isn’t possible to set mySQL’s timezone. I would be changing it for all the customers on my server which would suit me fine but may not make them happy.<\/p>\n

This means that using a mySQL function like NOW() generates a Texas timestamp rather than a London timestamp which isn’t what I want. There are a number of solutions to this problem, Richard Lord has written a good, clear post about possible solutions on his blog<\/a>.<\/p>\n

What I want is something that uses the timezone from Apache but stores the date in a mySQL format so I can use mySQL date comparisons when I retrieve the data later on. Fortunately the answer is quite straightforward; Where I would use NOW() to generate a timestamp I use the PHP function:<\/p>\n

date('Y-m-d H:i:s')<\/code><\/p>\n

This uses the current date from Apache (which is set to the London timezone) but converts it to the mySQL DATETIME format so that you can use mySQL’s date processing functions on the data. Of course if you had calculated some other date in your PHP code you could use the same function but pass your calculated date to the function instead of letting it use the current date\/time<\/p>\n

date('Y-m-d H:i:s',$my_date)<\/code><\/p>\n

So the SQL query you construct in PHP might look a bit like this:<\/p>\n

\n$query = 'INSERT INTO filename (\n          current_time_stamp,\n          my_time_stamp)\n          VALUES ("' . \n          date('Y-m-d H:i:s') . '", '" .          \/\/ replaces NOW()\n          date('Y-m-d H:i:s',$my_date) . '")';    \/\/ replaces calculated date\n<\/code><\/pre><\/p>\n
That solved my particular date\/time problem, I hope it helps you too.<\/p>\n","protected":false},"excerpt":{"rendered":"
My websites are hosted with Hostgator in Texas, USA but most of my customers are based in the UK. This creates a potential problem with dates and times because we usually want timestamps etc. to be recorded as if they were in the UK timezone rather than the timezone where the server is. Setting the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[90,19,18],"tags":[122,120,146,145,121],"_links":{"self":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/287"}],"collection":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/comments?post=287"}],"version-history":[{"count":6,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/287\/revisions"}],"predecessor-version":[{"id":293,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/287\/revisions\/293"}],"wp:attachment":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/media?parent=287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/categories?post=287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/tags?post=287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}