The issue of default value for timestamp(0)

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于timestamp(0)的默认值问题

| username: TiDBer_小阿飞

[TiDB Usage Environment] Test/
[TiDB Version] 6.5.3
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Issue Phenomenon and Impact]
CREATE TABLE testdm.event (
db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’,
name char(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘’,
body longblob NOT NULL,
definer char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’,
execute_at datetime(0) NULL DEFAULT NULL,
interval_value int(11) NULL DEFAULT NULL,
interval_field enum(‘YEAR’,‘QUARTER’,‘MONTH’,‘DAY’,‘HOUR’,‘MINUTE’,‘WEEK’,‘SECOND’,‘MICROSECOND’,‘YEAR_MONTH’,‘DAY_HOUR’,‘DAY_MINUTE’,‘DAY_SECOND’,‘HOUR_MINUTE’,‘HOUR_SECOND’,‘MINUTE_SECOND’,‘DAY_MICROSECOND’,‘HOUR_MICROSECOND’,‘MINUTE_MICROSECOND’,‘SECOND_MICROSECOND’) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
created timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
modified timestamp(0) NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
last_executed datetime(0) NULL DEFAULT NULL,
starts datetime(0) NULL DEFAULT NULL,
ends datetime(0) NULL DEFAULT NULL,
status enum(‘ENABLED’,‘DISABLED’,‘SLAVESIDE_DISABLED’) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘ENABLED’,
on_completion enum(‘DROP’,‘PRESERVE’) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘DROP’,
sql_mode set(‘REAL_AS_FLOAT’,‘PIPES_AS_CONCAT’,‘ANSI_QUOTES’,‘IGNORE_SPACE’,‘NOT_USED’,‘ONLY_FULL_GROUP_BY’,‘NO_UNSIGNED_SUBTRACTION’,‘NO_DIR_IN_CREATE’,‘POSTGRESQL’,‘ORACLE’,‘MSSQL’,‘DB2’,‘MAXDB’,‘NO_KEY_OPTIONS’,‘NO_TABLE_OPTIONS’,‘NO_FIELD_OPTIONS’,‘MYSQL323’,‘MYSQL40’,‘ANSI’,‘NO_AUTO_VALUE_ON_ZERO’,‘NO_BACKSLASH_ESCAPES’,‘STRICT_TRANS_TABLES’,‘STRICT_ALL_TABLES’,‘NO_ZERO_IN_DATE’,‘NO_ZERO_DATE’,‘INVALID_DATES’,‘ERROR_FOR_DIVISION_BY_ZERO’,‘TRADITIONAL’,‘NO_AUTO_CREATE_USER’,‘HIGH_NOT_PRECEDENCE’,‘NO_ENGINE_SUBSTITUTION’,‘PAD_CHAR_TO_FULL_LENGTH’) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘’,
comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’,
originator int(10) UNSIGNED NOT NULL,
time_zone char(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT ‘SYSTEM’,
character_set_client char(32) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
collation_connection char(32) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
db_collation char(32) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
body_utf8 longblob NULL,
PRIMARY KEY (db, name) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘Events’ ROW_FORMAT = Dynamic
Result: 1067 - Invalid default value for ‘modified’

The TIMESTAMP type includes date and time, with a supported range from 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999. The fsp parameter indicates the precision of seconds, with a range of 0 to 6, and the default value is 0. In TIMESTAMP, zeros are not allowed in the month or day parts, with the only exception being the zero value itself ‘0000-00-00 00:00:00’.
According to this document, zeros are not allowed in the month and day parts. Is this a TiDB rule or a TIMESTAMP type rule?
Original link: TIDB日期和时间类型_tidb 对比时间-CSDN博客

2
| username: TiDBer_小阿飞 | Original post link

The character set and collation of this line also won’t pass.

3
| username: tidb菜鸟一只 | Original post link

There is no problem with MySQL testing, but TiDB should not allow setting ‘0000-00-00 00:00:00’ as the default value for TIMESTAMP(0). You can change it like this:

CREATE TABLE t202(
    id INT,
    modified TIMESTAMP(0) NOT NULL,
    time_zone CHAR(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'SYSTEM'
);
4
| username: Miracle | Original post link

This should be related to sql_mode. In strict mode, it is not allowed to insert 0000.

5
| username: TiDBer_小阿飞 | Original post link

The only exception is the zero value itself ‘0000-00-00 00:00:00’

So how should this sentence be interpreted? :joy: :joy: :joy:

6
| username: Jellybean | Original post link

Looking at the original source, it is understood that in TIMESTAMP, zeros are not allowed in the month or day parts, with the only exception being the zero value itself 0000-00-00 00:00:00.

7
| username: Jellybean | Original post link
  • If the NO_ZERO_DATE SQL mode is disabled, TiDB allows the month or day in DATE and DATETIME columns to be zero. For example, 2009-00-00 or 2009-01-00. If you use functions to calculate such date types, such as using the DATE_SUB() or DATE_ADD() functions, the calculation results may be incorrect.
  • By default, TiDB enables the NO_ZERO_DATE SQL mode. This mode can prevent storing zero values like 0000-00-00.

The different types of zero values are shown in the table below:

Data Type Zero Value
DATE 0000-00-00
TIME 00:00:00
DATETIME 0000-00-00 00:00:00
TIMESTAMP 0000-00-00 00:00:00
YEAR 0000

If the SQL mode allows invalid DATE, DATETIME, TIMESTAMP values, the invalid values will be automatically converted to the corresponding zero values (0000-00-00 or 0000-00-00 00:00:00).

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.