Is there an equivalent to PHP's `date_default_timezone_set()` that works in MySQL?

Question

This works great in PHP

date_default_timezone_set('my/timezone');

$actualDate = date('Y-m-d');

Now when doing queries, I like to do

INSERT INTO articles (title, insert_date) VALUES ('My Title', now())

The problem with this is the now() in the MySQL is different to what it would be had it been calculated in PHP (and therefore against the timezone being set).

Is there a way, say a SQL query, to set the default timezone for MySQL?

Thanks

Answer 1

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

mysql> SET GLOBAL time_zone = timezone;

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

Answer 2

Something you could try is to store all of your date/times in UTC. So, you would use

date_default_timezone_set('UTC');
$actualDate = date('Y-m-d');

and

INSERT INTO articles (title, insert_date) VALUES ('My Title', UTC_TIMESTAMP())

Then you would convert the UTC date/time to the timezone of the user before displaying it.

Answer 3

I usually set the MySQL server to Greenwich Mean Time, and then use gmdate instead of date in PHP. Keeps everything in GM Time so if you move to another server, or have 2 in different time zones, things don't get all screwed up.