[MDEV-30633] DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow - Jira

XML

Word

Printable

Datetime to timestamp conversion currently converts huge datetime values to '0000-00-00 00:00:00':

CREATE OR REPLACE TABLE t1 (a TIMESTAMP NULL);

INSERT IGNORE INTO t1 VALUES ('3000-01-19 03:14:07');

SELECT a, UNIX_TIMESTAMP(a) FROM t1;

+---------------------+-------------------+

| a                   | UNIX_TIMESTAMP(a) |

+---------------------+-------------------+

| 0000-00-00 00:00:00 |                 0 |

+---------------------+-------------------+

This is not how other data types work.

Numeric data types convert huge values to the maximum value of the target data type:

CREATE OR REPLACE TABLE t1 (a TINYINT);

INSERT IGNORE INTO t1 VALUES (300);

SELECT a FROM t1;

+------+

| a    |

+------+

|  127 |

+------+

So does TIME:

CREATE OR REPLACE TABLE t1 (a TIME);

INSERT IGNORE INTO t1 VALUES ('999:00:00');

SELECT a FROM t1;

+-----------+

| a         |

+-----------+

| 838:59:59 |

+-----------+

Let's change DATETIME->TIMESTAMP conversion to convert huge DATETIME values to the maximum possible TIMESTAMP value with UNIX_TIMESTAMP 0x7FFFFFFF.

relates to

MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.