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

[MDEV-30633] DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow Created: 2023-02-10 Updated: 2024-01-24
Status:	Stalled
Project:	MariaDB Server
Component/s:	Data types, Temporal Types
Fix Version/s:	11.5

Type:

New Feature

Priority:

Major

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Unresolved

Votes:

Labels:

timestamp

Issue Links:

Relates
relates to	MDEV-15750	preserve MYSQL_TYPE_TIMESTAMP in temp...	Stalled

Description

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.

Comments

Comment by Alexander Barkov [ 2023-02-13 ]

Hello Sergei,

Generated at Thu Feb 08 10:17:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.