Description:
(Opening on behalf of)
Currently, when giving a DEFAULT for a TIMESTAMP field, the current
set time zone is used. It is also used for showing this default in
the SHOW CREATE TABLE. This means that whenever the time zone is
set differently, the output changes.
If the output has to be consistent, most people and most tools will
set the time zone session variable to, for example, UTC.
mysql> SET @@session.time_zone = "UTC"; -- Or +00:00
The above is known and since long how folks are doing it. It requires
no schema chance and is compatible throughout MySQL version down until
4.1.3.
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
The feature request is to allow the DEFAULT value for a TIMESTAMP
data type to be set as UTC, disregarding the session time zone.
Thus, instead of doing the usual:
mysql> SET @@session.time_zone = "UTC";
mysql> CREATE TABLE t1 (
ts TIMESTAMP DEFAULT "1970-01-01 00:00:01");
One would have no need to set the time zone variable:
mysql> CREATE TABLE t1 (
ts TIMESTAMP DEFAULT "1970-01-01 00:00:01Z");
Notice the Z, which means zero offset for UTC. Instead of Z it could
be UTC, or any timezone. It's just an example.
The above would be retained in the SHOW CREATE TABLE, and you would
get:
CREATE TABLE `t1` (
`ts` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01Z'
) ...
Regardless of what @@session.time_zone is set too.
Alternative solutions:
* allow the CONVERT_TZ()-function in the DEFAULT clause of the
field definition.
* add a server option which forces CREATE TABLE and SHOW CREATE
TABLE DEFAULT-values for TIMESTAMP datatypes to be UTC.
* add a TIME ZONE table option which will make sure the defaults
are shown and interpreted on creation with the set time zone.
Problems:
* Schema is incompatible with earlier releases of MySQL.
* It might give the false impression that timezone is stored into
the datatype, which is not the case.
* May solve confusion, but adds again confusion as what is shown
during a select, will not necessary show the default shown
in the SHOW CREATE TABLE output (if you miss the time zone).
* Change of behavior: tools that check for schema will break with
the addition of extra timezone information in DEFAULT.
It basically saves you a "SET @@session.time_zone = UTC".
And maybe (which I like) fix the invalid output when using
-XX:00 time zones (see 'How to repeat').
How to repeat:
mysql> SET @@session.time_zone = "UTC";
mysql> SHOW CREATE TABLE t1\G
.. CREATE TABLE `t1` (
`ts` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01'
) ..
mysql> SET @@session.time_zone = "Europe/Berlin";
mysql> SHOW CREATE TABLE t1\G
.. CREATE TABLE `t1` (
`ts` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01'
) ..
mysql> SET @@session.time_zone = "US/Pacific";
mysql> SHOW CREATE TABLE t1\G
.. CREATE TABLE `t1` (
`ts` timestamp NOT NULL DEFAULT '1969-12-31 16:00:01'
) ..
(Notice the invalid output for default of a TIMESTAMP data type: it's an impossible value according to docs; however, this works just fine.)
Suggested fix:
(see description)
