Changing the default time zone of a current timestamp in MySQL

I have a shared host which uses the time zone MST (GMT-7), and I cannot seem to change the time zone for my own database.

What is the best way to create a timestamp field in a table that has the default value be the current time in GMT-0?

Is setting the default value of the timestamp field to “DATE_SUB(NOW(), INTERVAL -7 HOUR)” a good idea?

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;

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

I have tried this… quite a few times. It just doesn’t have any effect. The current timestamp value always comes out in GMT-7. My host even told me themselves in an email response that I cannot change the time zone.

Alternatives…?

Works for me (server is EST5EDT):

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2008-07-19 03:08:37 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET TIME_ZONE = '-00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2008-07-19 07:10:13 |
+---------------------+
1 row in set (0.00 sec)

Yes, I am positive this does not work for me.

Your SQL query has been executed successfully (Query took 0.0002 sec)
SQL query:
SET TIME_ZONE = ‘-00:00’

CURRENT_TIMESTAMP()
2008-07-19 01:16:22

That should say 08:16:22. No matter how many times I run the SET TIME_ZONE command, it does nothing. The current timestamp is still in MST. It seems my host specifically denies me this ability.

So what else can I do to accomplish what I wrote in my first post?

Two choices… either use UNIX timestamps which are GMT by definition, or the CONVERT_TZ() function to reliably convert to GMT.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

I want to set the default value of the timestamp field to CONVERT_TZ(CURRENT_TIMESTAMP,‘-07:00’,‘+00:00’)

Why am I getting this error?

Error

SQL query:

ALTER TABLE `logins` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP,''-07:00'',''+00:00'')'

MySQL said: Documentation
#1067 - Invalid default value for 'timestamp'

mysql doesn’t support expressions in the DEFAULT clause. you can only use static values, or the ones specifically supported by the TIMESTAMP column.

So… is there any other way to do this… other than changing the INSERT queries in all my scripts to manually enter a timestamp value?