0

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?

  • Defaults cannot be dependent on external circumstances (the results from a query, session parameters, etc), they have to be constants. Perhaps you'd be better off with the ALLBALLS special timestamp value ('0000-00-00 00:00:00') which is not UTC dependent. See dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html – Ziggy Crueltyfree Zeitgeister May 23 '16 at 8:18
  • @ZiggyCrueltyfreeZeitgeister: If I try '0000-00-00 00:00:00' as the default value I get "ERROR 1067 (42000): Invalid default value for 'ts'". – Richard Hansen May 23 '16 at 21:39
1

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.

  • I need the date/time to reflect session timezone settings, so I need to use TIMESTAMP. I need the default to be the moment in time described by 1970-01-01 00:00:01 UTC (but displayed to the client according to the current value of @@session.time_zone). So this does not answer my question. – Richard Hansen May 23 '16 at 21:37
  • Yes, it does. It means, that all you have to do is create table foo(bar timestamp default '1970-01-01 00:00:01'); Nothing else. Is there something unclear in the text I quoted? – tombom May 24 '16 at 7:33
  • The string literal '1970-01-01 00:00:01' is not the same time as 1970-01-01 00:00:01 UTC unless @@session.time_zone is UTC—it is 1970-01-01 00:00:01 in the current time zone. That is not what I want. If the current time zone is east of +00:00 then '1970-01-01 00:00:01' is actually in 1969 UTC, which is outside the valid range of the TIMESTAMP type. – Richard Hansen May 24 '16 at 9:00
  • Oh, now I understand the problem. 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 whatever. Or just use datetime and store the time_zone in a separate column, I don't know your requirements. But there's no way you can make it work like you want to. – tombom May 24 '16 at 11:03
  • That's what I was afraid of. If you (or someone else) posts an answer saying "that's not possible" and mentions the alternatives you suggested (and maybe a way to do it with triggers, if that's possible), I'll accept the answer. – Richard Hansen May 24 '16 at 20:03
0

I believe DATETIME instead of TIMESTAMP does exactly what you are asking for, if I understood it correctly:

CREATE TABLE `foo` (`ts` DATETIME DEFAULT '1970-01-01 00:00:01');
  • I want to record the moment of time that is described by 1970-01-01 00:00:01 UTC. If I switched to DATETIME then I would have to modify some code to change how I process the value stored in the column. I'd like to avoid that if possible. What I asked seems like something that should be possible if not easy to do, but maybe it's not possible. – Richard Hansen May 23 '16 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.