MySQL – Non-Nullable Timestamp Column Without Default Value

MySQLmysql-5.7

I want a non-nullable timestamp column where the inserter is required to specify what the value should be.

The following configuration

CREATE TABLE test (
  t timestamp not null
);

results in the default CURRENT_TIMESTAMP value, while

CREATE TABLE test (
  t timestamp not null default null
);

results in

ERROR 1067 (42000): Invalid default value for 't'

I want the same behavior that, for example, the int column below has:

CREATE TABLE test (
  i int not null
);

Best Answer

I think that the only way is setting the server variable explicit_defaults_for_timestamp to ON (default is OFF).

This will disable the non-standard behaviour of timestamp columns regarding NULL values:

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

...

TIMESTAMP columns declared with the NOT NULL attribute do not permit NULL values. For inserts that specify NULL for such a column, the result is either an error for a single-row insert or if strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts with strict SQL mode disabled. In no case does assigning the column a value of NULL set it to the current timestamp.

Related Solutions

Mysql – Two nullable columns one required to have value

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

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

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.

Best Answer

Related Solutions

Mysql – Two nullable columns one required to have value

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

Related Question