MySQL – How to Set TIMESTAMP Default Value to 1970-01-01 00:00:01 UTC

default valueMySQLmysql-5.7timestamptimezone

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone?

I tried the following:

CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ(
    '1970-01-01 00:00:01', '+00.00', @@session.time_zone));

but got a syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near CONVERT_TZ('1970-01-01 00:00:01', '+00.00', @@session.time_zone)) at line 1

Is there any way to do this without creating a TRIGGER? If not, how would I do it with a TRIGGER?

Best Answer

EDIT after clarification:

I'm afraid you can't deal with it the way you intend.
You have to find another way, like for example defaulting to NULL and selecting via 

SELECT COALESCE(ts, CONVERT_TZ( '1970-01-01 00:00:01', '+00.00', @@session.time_zone)) AS ts, ...

Or just use datetime and store the time_zone in a separate column. It depends on your requirements. But there's no way you can make it work like you want to, not even with triggers.

Original answer:

You can't use any functions in a table definition.

What you're trying to do is unnecessary anyway. May I quote the manual:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

If you are querying a database across different timezones, and need the date/time to reflect individual users settings, use timestamp. If you need consistency regardless of timezone, use datetime.

Related Question