[MDEV-16991] Rounding vs truncation for TIME, DATETIME, TIMESTAMP - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.4.1
Component/s: Data types, Temporal Types
Labels:
None

Description

This task is a requirement for ~~MDEV-8894~~.

Currently MariaDB truncates fractional seconds when changing precision to smaller. This is different from how other databases work. Under terms of this task we'll add a way to do rounding instead of truncation. This will include

New C++ functions/methods to actually perform rounding for the temporal data types
A new session sql_mode flag TIME_ROUND_FRACTIONAL to force rounding vs truncation

Note, ~~MDEV-8894~~ will be fixed by a separate patch after this task.

In the new sql_mode, MariaDB will consistently do all operations (e.g. SET, CAST, ALTER) as follows:

Round when converting to TIME
Round when converting to DATETIME and TIMESTAMP
Truncate when converting to DATE

This is slightly different from how other databases work, but we believe this will give more intuitively expected results in most cases.

MySQL compatibility

The proposed way will give:

compatible behavior when converting to TIME, DATETIME, TIMESTAMP
incompatible behavior when converting to DATE

Later, when working on ~~MDEV-8894~~, we'll introduce a new sql_mode=MYSQL, which will emulate MySQL's rounding behavior precisely.
The current task is to introduce MariaDB's "native" mode for temporal rounding.

Oracle compatibility

Emulating precise Oracle rounding behavior when sql_mode is set to 'ORACLE,TIME_ROUND_FRACTIONAL' is out of scope of this task and will be done separately.

Summary on data type conversion in other databases

TD - target data type
SD - source data type

to TIME
Operation	TD	SD	MySQL	Oracle	PostgreSQL	SQL Standard
SET	time(3)	time(4)	round	N/A	round	Implementation defined
SET	time(3)	time(4)-in-varchar	round	N/A	error(wrong types)	error
SET	time(3)	time(7)-in-varchar	round	N/A	error(wrong types)	error
SET	time(3)	time(4)-in-decimal	round	N/A	error(wrong types)	error
SET	time(3)	time(7)-in-decimal	round	N/A	error(wrong types)	error
ALTER	time(3)	time(4)	round	N/A	round	error
ALTER	time(3)	time(4)-in-varchar	round	N/A	error(wrong types)	error
ALTER	time(3)	time(7)-in-varchar	round	N/A	error(wrong types)	error
ALTER	time(3)	time(4)-in-decimal	round	N/A	error(wrong types)	error
ALTER	time(3)	time(7)-in-decimal	round	N/A	error(wrong types)	error
ALTER	time(6)	time(7)-in-decimal	round	N/A	error(wrong types)	error
CAST	time(3)	time(4) literal	round	N/A	round	Implementation defined
CAST	time(3)	time(4)-in-string	round	N/A	round	error
CAST	time(3)	time(7)-in-string	round	N/A	round	error
CAST	time(3)	time(4)-in-decimal	round	N/A	error(wrong types)	error
CAST	time(3)	time(7)-in-decimal	round	N/A	error(wrong types)	error
to TIMESTAMP
Operation	TD	SD	MySQL	Oracle	PostgreSQL	SQL Standard
SET	timestamp(3)	timestamp(4)	round	round	round	Implementation defined
SET	timestamp(3)	timestamp(4)-in-varchar	round	round	error(wrong types)	error
SET	timestamp(3)	timestamp(7)-in-varchar	round	round	error(wrong types)	error
SET	timestamp(3)	timestamp(4)-in-decimal	round	error(wrong types)	error(wrong types)	error
SET	timestamp(3)	timestamp(7)-in-decimal	round	error(wrong types)	error(wrong types)	error
ALTER	timestamp(3)	timestamp(4)	round	error	round	error
ALTER	timestamp(3)	timestamp(4)-in-varchar	round	error(column must be empty)	error(wrong types)	error
ALTER	timestamp(3)	timestamp(7)-in-varchar	round	error(column must be empty)	error(wrong types)	error
ALTER	timestamp(3)	timestamp(4)-in-decimal	round	error(column must be empty)	error(wrong types)	error
ALTER	timestamp(3)	timestamp(7)-in-decimal	round	error(column must be empty)	error(wrong types)	error
CAST	timestamp(3)	timestamp(4) literal	round	round	round	Implementation defined
CAST	timestamp(3)	timestamp(4)-in-string	round	round	round	error
CAST	timestamp(3)	timestamp(7)-in-string	round	round	round	error
CAST	timestamp(3)	timestamp(4)-in-decimal	round	error(wrong types)	error(wrong types)	error
CAST	timestamp(3)	timestamp(7)-in-decimal	round	error(wrong types	error(wrong types)	error
to DATE
Operation	TD	SD	MySQL	Oracle	PostgreSQL	SQL Standard
SET	date	timestamp(4)	round	truncate	truncate	error
SET	date	timestamp(4)-in-varchar	round	error(wrong format)	error(wrong types)	error
SET	date	timestamp(7)-in-varchar	round	error(wrong format)	error(wrong types)	error
SET	date	timestamp(4)-in-decimal	round	error(wrong types)	error(wrong types)	error
SET	date	timestamp(7)-in-decimal	round	error(wrong format)	error(wrong types)	error
ALTER	date	timestamp(4)	round	round	truncate	error
ALTER	date	timestamp(4)-in-varchar	round	error(must be empty)	error(wrong types)	error
ALTER	date	timestamp(7)-in-varchar	round	error(must be empty)	error(wrong types)	error
ALTER	date	timestamp(4)-in-decimal	round	error(must be empty)	error(wrong types)	error
ALTER	date	timestamp(7)-in-decimal	round	error(must be empty)	error(wrong types)	error
CAST	date	timestamp(4) literal	truncate	truncate	truncate	truncate
CAST	date	timestamp(4)-in-string	truncate	error(wrong format)	truncate	error
CAST	date	timestamp(7)-in-string	round!	error(wrong format)	truncate	error
CAST	date	timestamp(4)-in-decimal	truncate	error(wrong types)	error(wrong types)	error
CAST	date	timestamp(7)-in-decimal	round!	error(wrong types)	error(wrong types)	error

Observations:

MySQL is not consistent about conversion from varchar to date (reported as MySQL Bug #92475):
- implicit cast always rounds
- explicit CAST truncates for FSP<=6
- explicit CAST rounds for FSP>6
MySQL: CAST(AS DATE) truncates microseconds but rounds nanoseconds

Attachments

Issue Links

blocks

MDEV-6001 Merge tests for MySQL WL#946 TIME/DATETIME/TIMESTAMP with fractional precision

Stalled

MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

Closed

is blocked by

MDEV-15406 NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation

Closed

MDEV-17175 Change Time/Datetime constructors to return warnings in MYSQL_TIME_STATUS

Closed

MDEV-17182 Move fractional second truncation outside of Field_xxx::store_TIME_with_warn()

Closed

MDEV-17203 Move fractional second truncation from Item_xxx_typecast::get_date() to Time and Datetime constructors

Closed

MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result

Closed

MDEV-17274 Split Field_temporal_with_date::store*() for Field_date_common and Field_datetime

Closed

MDEV-17288 Replace Item_func::get_arg0_date() to Date/Datetime methods

Closed

MDEV-17317 Add THD* parameter into Item::get_date() and stricter data type control to "fuzzydate"

Closed

MDEV-17331 Reuse Temporal_hybrid in xxx_to_date_with_warn()

Closed

MDEV-17351 Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument

Closed

MDEV-17400 The result of TIME('42949672965959-01') depends on architecture

Closed

MDEV-17417 TIME(99991231235959) returns 838:59:59 instead of 23:59:59

Closed

MDEV-17434 EXTRACT(DAY FROM negative_time) returns wrong result

Closed

MDEV-17460 Move the code from Item_extract::val_int() to a new class Extract_source

Closed

MDEV-17477 Wrong result for TIME('-2001-01-01 10:20:30') and numerous other str-to-time conversion problems

Closed

MDEV-17478 Wrong result for TIME('+100:20:30')

Closed

MDEV-17712 Remove C_TIME_FUZZY_DATES, C_TIME_DATETIME_ONLY, C_TIME_TIME_ONLY

Closed

MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))

Closed

MDEV-17792 New class Timestamp and cleanups in Date, Datetime, Field for rounding

Closed

relates to

MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result

Closed

MDEV-17325 NULL-ability problems with LEAST() in combination with NO_ZERO_DATE and NO_ZERO_IN_DATE

Closed

MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument

Open

MDEV-17418 Inconsitent results for TIME(8395960) and TIME('839:59:60')

Open

MDEV-17836 Document Rounding vs truncation for TIME, DATETIME, TIMESTAMP

Open

MCOL-4421 Add TIME_ROUND_FRACTIONAL support in ColumnStore

Open

MDEV-17329 Inconsistency of NULL date value conversion to zero date in comparison context

Open

MDEV-19502 TIME_ROUND_FRACTIONAL is not respected on TIME->BIGINT conversion

Open

links to

MySQL Bug#92473 DATE_ADD rounds nanoseconds inconsistently

MySQL Bug#92474 STR_TO_DATE truncates bad microseconds unexpectedly

MySQL Bug#92475 CAST(AS DATE) truncates microseconds but rounds nanoseconds

MySQL bug#92501 FROM_UNIXTIME accepts an out of range parameter through rounding

(16 is blocked by, 8 relates to, 4 links to)

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-09-12 15:30 - edited

DATETIME to TIMESTAMP conversion with rounding in MySQL

We will preserve this behavior.

When inserting a DATETIME representation to a TIMESTAMP column, MySQL first rounds DATETIME representation, then converts the result to unix timestamp format.

The following script proves that.

The last second of the winter time is inserted into the table without a problem:

SET sql_mode=STRICT_ALL_TABLES;

SET time_zone='Europe/Moscow';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIMESTAMP);

INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0');

However, if I now add non-zero fractional seconds, the value rounds to '2010-03-28 02:00:00' (which falls to the DST gap) and refuses to insert:

INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9');

ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:59:59.9' for column 'a' at row 1

If processing performed in other other order (convert to unix timestamp first, then round unix timestamp representation), the last query would not fail.

In non-strict mode it works as follows:

SET sql_mode='';

SET time_zone='Europe/Moscow';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIMESTAMP);

INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0');

INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9');

SELECT a, UNIX_TIMESTAMP(a) FROM t1;

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

| a                   | UNIX_TIMESTAMP(a) |

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

| 2010-03-28 01:59:59 |        1269730799 |

| 2010-03-28 03:00:00 |        1269730800 |

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

(i.e. the value that falls to the DST gap is converted to the first second after the gap).

Alexander Barkov added a comment - 2018-09-12 15:30 - edited DATETIME to TIMESTAMP conversion with rounding in MySQL We will preserve this behavior. When inserting a DATETIME representation to a TIMESTAMP column, MySQL first rounds DATETIME representation, then converts the result to unix timestamp format. The following script proves that. The last second of the winter time is inserted into the table without a problem: SET sql_mode=STRICT_ALL_TABLES; SET time_zone= 'Europe/Moscow' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIMESTAMP ); INSERT INTO t1 VALUES ( '2010-03-28 01:59:59.0' ); However, if I now add non-zero fractional seconds, the value rounds to '2010-03-28 02:00:00' (which falls to the DST gap) and refuses to insert: INSERT INTO t1 VALUES ( '2010-03-28 01:59:59.9' ); ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:59:59.9' for column 'a' at row 1 If processing performed in other other order (convert to unix timestamp first, then round unix timestamp representation), the last query would not fail. In non-strict mode it works as follows: SET sql_mode= '' ; SET time_zone= 'Europe/Moscow' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIMESTAMP ); INSERT INTO t1 VALUES ( '2010-03-28 01:59:59.0' ); INSERT INTO t1 VALUES ( '2010-03-28 01:59:59.9' ); SELECT a, UNIX_TIMESTAMP(a) FROM t1; +---------------------+-------------------+ | a | UNIX_TIMESTAMP(a) | +---------------------+-------------------+ | 2010-03-28 01:59:59 | 1269730799 | | 2010-03-28 03:00:00 | 1269730800 | +---------------------+-------------------+ (i.e. the value that falls to the DST gap is converted to the first second after the gap).

Alexander Barkov added a comment - 2018-09-18 04:34 - edited

Details on how various functions work in other databases

EXTRACT(SECOND)

SELECT

  extract(SECOND FROM timestamp'2001-12-31 23:59:59.999999') AS c6,

  extract(SECOND FROM timestamp'2001-12-31 23:59:59.9999999') AS c7,

  extract(SECOND FROM timestamp'2001-12-31 23:59:59.99999999') AS c8,

  extract(SECOND FROM timestamp'2001-12-31 23:59:59.999999999') AS c9

FROM dual;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   59 |    0 |    0 |    0 |

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

Oracle:

	C6	   C7	      C8	 C9

---------- ---------- ---------- ----------

 59.999999 59.9999999	      60	 60

PostgreSQL:

    c6     | c7 | c8 | c9

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

 59.999999 |  0 |  0 |  0

SELECT

  extract(SECOND FROM '2001-12-31 23:59:59.999999') AS c6,

  extract(SECOND FROM '2001-12-31 23:59:59.9999999') AS c7,

  extract(SECOND FROM '2001-12-31 23:59:59.99999999') AS c8,

  extract(SECOND FROM '2001-12-31 23:59:59.999999999') AS c9

FROM dual;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   59 |    0 |    0 |    0 |

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

Oracle:

ORA-30076: invalid extract field for extract source

PostgreSQL:

ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Summary:

MySQL and PostgreSQL round nanoseconds
Oracle does something unclear

EXTRACT(MINUTE)

SELECT

  extract(MINUTE FROM timestamp'2001-12-31 23:59:59.999999') AS c6,

  extract(MINUTE FROM timestamp'2001-12-31 23:59:59.9999999') AS c7,

  extract(MINUTE FROM timestamp'2001-12-31 23:59:59.99999999') AS c8,

  extract(MINUTE FROM timestamp'2001-12-31 23:59:59.999999999') AS c9

FROM dual;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   59 |    0 |    0 |    0 |

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

Oracle

	C6	   C7	      C8	 C9

---------- ---------- ---------- ----------

	59	   59	      59	 59

PostgreSQL

 c6 | c7 | c8 | c9

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

 59 |  0 |  0 |  0

Summary:

MySQL and PostgreSQL round nanoseconds
Oracle preserves the minute value (as it supports up to 9 fractional digits)

SELECT

  extract(MINUTE FROM '2001-12-31 23:59:59.999999') AS c6,

  extract(MINUTE FROM '2001-12-31 23:59:59.9999999') AS c7,

  extract(MINUTE FROM '2001-12-31 23:59:59.99999999') AS c8,

  extract(MINUTE FROM '2001-12-31 23:59:59.999999999') AS c9

FROM dual;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   59 |    0 |    0 |    0 |

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

Oracle:

ORA-30076: invalid extract field for extract source

PostgreSQL:

ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

EXTRACT(DAY)

SELECT

  extract(DAY FROM '2001-12-31 23:59:59.999999') AS c6,

  extract(DAY FROM '2001-12-31 23:59:59.9999999') AS c7,

  extract(DAY FROM '2001-12-31 23:59:59.99999999') AS c8,

  extract(DAY FROM '2001-12-31 23:59:59.999999999') AS c9

FROM dual;

MySQL

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

| c6   | c7   | c8   | c9   |

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

|   31 |    1 |    1 |    1 |

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

Oracle

ORA-30076: invalid extract field for extract source

PostgreSQL

ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

MINUTE()

SELECT

  MINUTE('2001-12-31 23:59:59.999999') AS c6,

  MINUTE('2001-12-31 23:59:59.9999999') AS c7,

  MINUTE('2001-12-31 23:59:59.99999999') AS c8,

  MINUTE('2001-12-31 23:59:59.999999999') AS c9;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   59 |    0 |    0 |    0 |

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

Summary:

MySQL rounds nanoseconds

DAY()

SELECT

  DAY('2001-12-31 23:59:59.999999') AS c6,

  DAY('2001-12-31 23:59:59.9999999') AS c7,

  DAY('2001-12-31 23:59:59.99999999') AS c8,

  DAY('2001-12-31 23:59:59.999999999') AS c9;

MySQL:

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

| c6   | c7   | c8   | c9   |

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

|   31 |    1 |    1 |    1 |

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

Summary:

MySQL rounds nanoseconds

TIME_TO_SEC()

SELECT

  time_to_sec('00:00:00.999999') AS c6,

  time_to_sec('00:00:00.9999999') AS c7;

MySQL:

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

| c6   | c7   |

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

|    0 |    1 |

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

Summary:

MySQL rounds nanoseconds

Alexander Barkov added a comment - 2018-09-18 04:34 - edited Details on how various functions work in other databases EXTRACT(SECOND) SELECT extract( SECOND FROM timestamp '2001-12-31 23:59:59.999999' ) AS c6, extract( SECOND FROM timestamp '2001-12-31 23:59:59.9999999' ) AS c7, extract( SECOND FROM timestamp '2001-12-31 23:59:59.99999999' ) AS c8, extract( SECOND FROM timestamp '2001-12-31 23:59:59.999999999' ) AS c9 FROM dual; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 59 | 0 | 0 | 0 | +------+------+------+------+ Oracle: C6 C7 C8 C9 ---------- ---------- ---------- ---------- 59.999999 59.9999999 60 60 PostgreSQL: c6 | c7 | c8 | c9 -----------+----+----+---- 59.999999 | 0 | 0 | 0 SELECT extract( SECOND FROM '2001-12-31 23:59:59.999999' ) AS c6, extract( SECOND FROM '2001-12-31 23:59:59.9999999' ) AS c7, extract( SECOND FROM '2001-12-31 23:59:59.99999999' ) AS c8, extract( SECOND FROM '2001-12-31 23:59:59.999999999' ) AS c9 FROM dual; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 59 | 0 | 0 | 0 | +------+------+------+------+ Oracle: ORA-30076: invalid extract field for extract source PostgreSQL: ERROR: function pg_catalog.date_part(unknown, unknown) is not unique HINT: Could not choose a best candidate function. You might need to add explicit type casts. Summary: MySQL and PostgreSQL round nanoseconds Oracle does something unclear EXTRACT(MINUTE) SELECT extract( MINUTE FROM timestamp '2001-12-31 23:59:59.999999' ) AS c6, extract( MINUTE FROM timestamp '2001-12-31 23:59:59.9999999' ) AS c7, extract( MINUTE FROM timestamp '2001-12-31 23:59:59.99999999' ) AS c8, extract( MINUTE FROM timestamp '2001-12-31 23:59:59.999999999' ) AS c9 FROM dual; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 59 | 0 | 0 | 0 | +------+------+------+------+ Oracle C6 C7 C8 C9 ---------- ---------- ---------- ---------- 59 59 59 59 PostgreSQL c6 | c7 | c8 | c9 ----+----+----+---- 59 | 0 | 0 | 0 Summary: MySQL and PostgreSQL round nanoseconds Oracle preserves the minute value (as it supports up to 9 fractional digits) SELECT extract( MINUTE FROM '2001-12-31 23:59:59.999999' ) AS c6, extract( MINUTE FROM '2001-12-31 23:59:59.9999999' ) AS c7, extract( MINUTE FROM '2001-12-31 23:59:59.99999999' ) AS c8, extract( MINUTE FROM '2001-12-31 23:59:59.999999999' ) AS c9 FROM dual; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 59 | 0 | 0 | 0 | +------+------+------+------+ Oracle: ORA-30076: invalid extract field for extract source PostgreSQL: ERROR: function pg_catalog.date_part(unknown, unknown) is not unique HINT: Could not choose a best candidate function. You might need to add explicit type casts. EXTRACT(DAY) SELECT extract( DAY FROM '2001-12-31 23:59:59.999999' ) AS c6, extract( DAY FROM '2001-12-31 23:59:59.9999999' ) AS c7, extract( DAY FROM '2001-12-31 23:59:59.99999999' ) AS c8, extract( DAY FROM '2001-12-31 23:59:59.999999999' ) AS c9 FROM dual; MySQL +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 31 | 1 | 1 | 1 | +------+------+------+------+ Oracle ORA-30076: invalid extract field for extract source PostgreSQL ERROR: function pg_catalog.date_part(unknown, unknown) is not unique HINT: Could not choose a best candidate function. You might need to add explicit type casts. MINUTE() SELECT MINUTE ( '2001-12-31 23:59:59.999999' ) AS c6, MINUTE ( '2001-12-31 23:59:59.9999999' ) AS c7, MINUTE ( '2001-12-31 23:59:59.99999999' ) AS c8, MINUTE ( '2001-12-31 23:59:59.999999999' ) AS c9; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 59 | 0 | 0 | 0 | +------+------+------+------+ Summary: MySQL rounds nanoseconds DAY() SELECT DAY ( '2001-12-31 23:59:59.999999' ) AS c6, DAY ( '2001-12-31 23:59:59.9999999' ) AS c7, DAY ( '2001-12-31 23:59:59.99999999' ) AS c8, DAY ( '2001-12-31 23:59:59.999999999' ) AS c9; MySQL: +------+------+------+------+ | c6 | c7 | c8 | c9 | +------+------+------+------+ | 31 | 1 | 1 | 1 | +------+------+------+------+ Summary: MySQL rounds nanoseconds TIME_TO_SEC() SELECT time_to_sec( '00:00:00.999999' ) AS c6, time_to_sec( '00:00:00.9999999' ) AS c7; MySQL: +------+------+ | c6 | c7 | +------+------+ | 0 | 1 | +------+------+ Summary: MySQL rounds nanoseconds

Alexander Barkov added a comment - 2018-09-18 09:27 - edited

Other functions with date input that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

YEAR
MONTH
DAY
EXTRACT(DAY|MONTH|YEAR)
DAYOFMONTH
DAYOFYEAR
TO_DAYS
QUARTER,
WEEK
YEARWEEK
WEEKDAY
MONTHNAME
DAYNAME
LAST_DAY

Other functions with datetime/time input that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

SECOND
MINUTE
HOUR
EXTRACT(SECOND|MINUTE|HOUR)
TO_SECONDS
UNIX_TIMESTAMP
TIME_TO_SEC
CONVERT_TZ

Functions that construct TIME and round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

FROM_UNIXTIME
MAKETIME
SEC_TO_TIME

Other functions that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

DATE_FORMAT

Other dyadic functions that round nanoseconds in MySQL for both arguments

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

TIMESTAMP
ADDTIME
TIMEDIFF
TIMESTAMPDIFF

FROM_UNIXTIME is questionable in MySQL.

It generally rounds nanoseconds. However, it can go outside of the supported TIMESTAMP range.

SET time_zone='+00:00';

SELECT

  FROM_UNIXTIME(2147483647) AS c1,

  FROM_UNIXTIME(2147483648) AS c2,

  FROM_UNIXTIME(2147483647.9999999) AS c3;

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

| c1                  | c2   | c3                         |

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

| 2038-01-19 03:14:07 | NULL | 2038-01-19 03:14:08.000000 |

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

Notice, the value in c3 is probably wrong. It should be NULL instead.
This was reported in MySQL Bug #92501.

MariaDB will round nanoseconds, but will return `NULL` instead of going out of the range:

SET sql_mode=TIME_ROUND_FRACTIONAL;

SET time_zone='+00:00';

SELECT

  FROM_UNIXTIME(2147483647) AS c1,

  FROM_UNIXTIME(2147483648) AS c2,

  FROM_UNIXTIME(2147483647.9999999) AS c3;

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

| c1                  | c2   | c3   |

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

| 2038-01-19 03:14:07 | NULL | NULL |

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

STR_TO_DATE is questionable in MySQL

SELECT STR_TO_DATE('2017-12-31 23:59:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS c7;

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

| c7                         |

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

| 2017-12-31 23:59:59.999999 |

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

Note, nanoseconds are truncated, which looks inconsistent.
Reported as MySQL Bug #92474.

MariaDB will do the same way with MySQL for now.

DATE_ADD is questionable in MySQL

DATE_ADD rounds nanoseconds, but only for the first argument.

SELECT

  DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70,

  DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07;

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

| c70                 | c07                        |

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

| 2018-01-01 00:00:00 | 2017-12-31 23:59:59.999999 |

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

Looks inconsistent.

Note, the dyadic function TIMESTAMP rounds nanoseconds for both arguments:

SELECT

  TIMESTAMP('2017-12-31 23:59:59.9999999', '00:00:00') AS c70,

  TIMESTAMP('2017-12-31 23:59:59', '00:00:00.9999999') AS c07;

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

| c70                        | c07                        |

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

| 2018-01-01 00:00:00.000000 | 2018-01-01 00:00:00.000000 |

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

This was reported as MySQL Bug#92473.

MariaDB will reproduce MySQL behavior when TIME_ROUND_FRACTIONAL is set, but we should probably revise this in the future.

Alexander Barkov added a comment - 2018-09-18 09:27 - edited Other functions with date input that round nanoseconds in MySQL MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set YEAR MONTH DAY EXTRACT(DAY|MONTH|YEAR) DAYOFMONTH DAYOFYEAR TO_DAYS QUARTER, WEEK YEARWEEK WEEKDAY MONTHNAME DAYNAME LAST_DAY Other functions with datetime/time input that round nanoseconds in MySQL MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set SECOND MINUTE HOUR EXTRACT(SECOND|MINUTE|HOUR) TO_SECONDS UNIX_TIMESTAMP TIME_TO_SEC CONVERT_TZ Functions that construct TIME and round nanoseconds in MySQL MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set FROM_UNIXTIME MAKETIME SEC_TO_TIME Other functions that round nanoseconds in MySQL MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set DATE_FORMAT Other dyadic functions that round nanoseconds in MySQL for both arguments MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set TIMESTAMP ADDTIME TIMEDIFF TIMESTAMPDIFF FROM_UNIXTIME is questionable in MySQL. It generally rounds nanoseconds. However, it can go outside of the supported TIMESTAMP range. SET time_zone= '+00:00' ; SELECT FROM_UNIXTIME(2147483647) AS c1, FROM_UNIXTIME(2147483648) AS c2, FROM_UNIXTIME(2147483647.9999999) AS c3; +---------------------+------+----------------------------+ | c1 | c2 | c3 | +---------------------+------+----------------------------+ | 2038-01-19 03:14:07 | NULL | 2038-01-19 03:14:08.000000 | +---------------------+------+----------------------------+ Notice, the value in c3 is probably wrong. It should be NULL instead. This was reported in MySQL Bug #92501. MariaDB will round nanoseconds, but will return NULL instead of going out of the range: SET sql_mode=TIME_ROUND_FRACTIONAL; SET time_zone= '+00:00' ; SELECT FROM_UNIXTIME(2147483647) AS c1, FROM_UNIXTIME(2147483648) AS c2, FROM_UNIXTIME(2147483647.9999999) AS c3; +---------------------+------+------+ | c1 | c2 | c3 | +---------------------+------+------+ | 2038-01-19 03:14:07 | NULL | NULL | +---------------------+------+------+ STR_TO_DATE is questionable in MySQL SELECT STR_TO_DATE( '2017-12-31 23:59:59.9999999' , '%Y-%m-%d %H:%i:%s.%f' ) AS c7; + ----------------------------+ | c7 | + ----------------------------+ | 2017-12-31 23:59:59.999999 | + ----------------------------+ Note, nanoseconds are truncated, which looks inconsistent. Reported as MySQL Bug #92474. MariaDB will do the same way with MySQL for now. DATE_ADD is questionable in MySQL DATE_ADD rounds nanoseconds, but only for the first argument. SELECT DATE_ADD( '2017-12-31 23:59:59.9999999' , INTERVAL 0 SECOND ) AS c70, DATE_ADD( '2017-12-31 23:59:59' , INTERVAL 0.9999999 SECOND ) AS c07; +---------------------+----------------------------+ | c70 | c07 | +---------------------+----------------------------+ | 2018-01-01 00:00:00 | 2017-12-31 23:59:59.999999 | +---------------------+----------------------------+ Looks inconsistent. Note, the dyadic function TIMESTAMP rounds nanoseconds for both arguments: SELECT TIMESTAMP ( '2017-12-31 23:59:59.9999999' , '00:00:00' ) AS c70, TIMESTAMP ( '2017-12-31 23:59:59' , '00:00:00.9999999' ) AS c07; +----------------------------+----------------------------+ | c70 | c07 | +----------------------------+----------------------------+ | 2018-01-01 00:00:00.000000 | 2018-01-01 00:00:00.000000 | +----------------------------+----------------------------+ This was reported as MySQL Bug#92473. MariaDB will reproduce MySQL behavior when TIME_ROUND_FRACTIONAL is set, but we should probably revise this in the future.

Alexander Barkov added a comment - 2018-11-21 03:09 - edited

Details on how rounding works on SET assignment in other databases

SET from TIME to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(3), b TIME(4));

INSERT INTO t1 VALUES (NULL, '00:00:01.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A (no TIME data type)
PgSQL: 00:00:02

SET from TIMESTAMP to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(3), b TIMESTAMP(4));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: 2001-01-02 00:00:00

SET from TIMESTAMP to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a DATE, b TIMESTAMP(4));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: 2001-01-01
PgSQL: 2001-01-01

SET from TIME-alike-VARCHAR to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(3), b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '00:00:01.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A
PgSQL: error (wrong types)

SET from TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(3), b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: error (wrong types)

SET from TIMESTAMP-alike-VARCHAR to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a DATE, b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: error (wrong format)
PgSQL: error (wrong types)

SET from TIME(7)-in-VARCHAR to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(3), b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '00:00:01.9999999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A
PgSQL: error(wrong types)

SET from TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(3), b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: error(wrong types)

SET from TIMESTAMP(7)-in-VARCHAR to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a DATE, b VARCHAR(64));

INSERT INTO t1 VALUES (NULL, '2001-01-01 23:59:59.9999999');

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: error(wrong format)
PgSQL: error(wrong types)

SET from TIME(4)-in-DECIMAL to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(3), b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 1.9999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A
PgSQL: ERROR: column "a" is of type time without time zone but expression is of type numeric

SET from TIMESTAMP(4)-in-DECIMAL to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(3), b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 20010101235959.9999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
PgSQL: ERROR: column "a" is of type timestamp without time zone but expression is of type numeric

SET from TIMESTAMP(4)-in-DECIMAL to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a DATE, b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 20010101235959.9999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
PgSQL: ERROR: column "a" is of type date but expression is of type numeric

SET from TIME(7)-in-DECIMAL to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(3), b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 000001.9999999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 00:00:02.000
Oracle: N/A
PgSQL: ERROR: column "a" is of type time without time zone but expression is of type numeric

SET from TIMESTAMP(7)-in-DECIMAL to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(3), b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 20010101235959.9999999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00.000
Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
PgSQL: ERROR: column "a" is of type timestamp without time zone but expression is of type numeric

SET from TIMESTAMP(7)-in-DECIMAL to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

-- MySQL, Oracle, PgSQL

DROP TABLE t1;

CREATE TABLE t1 (a DATE, b DECIMAL(38,10));

INSERT INTO t1 VALUES (NULL, 20010101235959.9999999);

UPDATE t1 SET a=b;

SELECT a FROM t1;

Alexander Barkov added a comment - 2018-11-21 03:09 - edited Details on how rounding works on SET assignment in other databases SET from TIME to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (3), b TIME (4)); INSERT INTO t1 VALUES ( NULL , '00:00:01.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A (no TIME data type) PgSQL: 00:00:02 SET from TIMESTAMP to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3), b TIMESTAMP (4)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: 2001-01-02 00:00:00 SET from TIMESTAMP to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a DATE , b TIMESTAMP (4)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 Oracle: 2001-01-01 PgSQL: 2001-01-01 SET from TIME-alike-VARCHAR to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (3), b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '00:00:01.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A PgSQL: error (wrong types) SET from TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3), b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: error (wrong types) SET from TIMESTAMP-alike-VARCHAR to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a DATE , b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 Oracle: error (wrong format) PgSQL: error (wrong types) SET from TIME(7)-in-VARCHAR to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (3), b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '00:00:01.9999999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A PgSQL: error(wrong types) SET from TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3), b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: error(wrong types) SET from TIMESTAMP(7)-in-VARCHAR to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a DATE , b VARCHAR (64)); INSERT INTO t1 VALUES ( NULL , '2001-01-01 23:59:59.9999999' ); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 Oracle: error(wrong format) PgSQL: error(wrong types) SET from TIME(4)-in-DECIMAL to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (3), b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 1.9999); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A PgSQL: ERROR: column "a" is of type time without time zone but expression is of type numeric SET from TIMESTAMP(4)-in-DECIMAL to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3), b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 20010101235959.9999); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER PgSQL: ERROR: column "a" is of type timestamp without time zone but expression is of type numeric SET from TIMESTAMP(4)-in-DECIMAL to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a DATE , b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 20010101235959.9999); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER PgSQL: ERROR: column "a" is of type date but expression is of type numeric SET from TIME(7)-in-DECIMAL to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (3), b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 000001.9999999); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 00:00:02.000 Oracle: N/A PgSQL: ERROR: column "a" is of type time without time zone but expression is of type numeric SET from TIMESTAMP(7)-in-DECIMAL to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3), b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 20010101235959.9999999); UPDATE t1 SET a=b; SELECT a FROM t1; MySQL: 2001-01-02 00:00:00.000 Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER PgSQL: ERROR: column "a" is of type timestamp without time zone but expression is of type numeric SET from TIMESTAMP(7)-in-DECIMAL to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; -- MySQL, Oracle, PgSQL DROP TABLE t1; CREATE TABLE t1 (a DATE , b DECIMAL (38,10)); INSERT INTO t1 VALUES ( NULL , 20010101235959.9999999); UPDATE t1 SET a=b; SELECT a FROM t1;

Alexander Barkov added a comment - 2018-11-21 03:10

Details on how rounding works on ALTER in other databases

ALTER from TIME to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a TIME(4));

INSERT INTO t1 VALUES ('00:00:01.9999');

ALTER TABLE t1 MODIFY a TIME(3);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A (No TIME data type)
PgSQL: 00:00:02

ALTER TIMESTAMP to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(4));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999');

ALTER TABLE t1 MODIFY a TIMESTAMP(3);               -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision
PgSQL: 2001-01-02 00:00:00

ALTER TIMESTAMP to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a TIMESTAMP(4));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999');

ALTER TABLE t1 MODIFY a DATE;                        -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE DATE;          -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: 2001-01-02
PgSQL: 2001-01-01

ALTER from TIME(7)-in-VARCHAR to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('00:00:01.9999999');

ALTER TABLE t1 MODIFY a TIME(3);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A
PgSQL: column "a" cannot be cast automatically to type time without time zone

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('00:00:01.9999999');

ALTER TABLE t1 MODIFY a TIME(6);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(6); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02.000000
Oracle: N/A
PgSQL:" ERROR: column "a" cannot be cast automatically

ALTER TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999999');

ALTER TABLE t1 MODIFY a TIMESTAMP(3);               -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: ERROR: column "a" cannot be cast automatically to type timestamp without time zone

MySQL: 00:00:02.000000
Oracle: N/A
PgSQL:" ERROR: column "a" cannot be cast automatically

ALTER TIMESTAMP(7)-in-VARCHAR to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999999');

ALTER TABLE t1 MODIFY a DATE;                        -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE DATE;          -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: ERROR: column "a" cannot be cast automatically to type date

ALTER from TIME-alike-VARCHAR to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('00:00:01.9999');

ALTER TABLE t1 MODIFY a TIME(3);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: N/A
PgSQL: error (wrong types)

ALTER TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999');

ALTER TABLE t1 MODIFY a TIMESTAMP(3);               -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: error (column must be empty
PgSQL: error (wrong types)

ALTER TIMESTAMP-alike-VARCHAR to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));

INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9999');

ALTER TABLE t1 MODIFY a DATE;                        -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE DATE;          -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: error (column must be empty)
PgSQL: error (wrong types)

ALTER from TIME(4)-alike-DECIMAL to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (1.9999);

ALTER TABLE t1 MODIFY a TIME(3);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: ORA-01735: invalid ALTER TABLE option
PgSQL: error(wrong types)

ALTER TIMESTAMP(4)-alike-DECIMAL to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (20010101235959.9999);

ALTER TABLE t1 MODIFY a TIMESTAMP(3);               -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: error(wrong types)

ALTER TIMESTAMP(4)-alike-DECIMAL to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (20010101235959.9999);

ALTER TABLE t1 MODIFY a DATE;                        -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE DATE;          -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: error(wrong types)

ALTER from TIME(7)-alike-DECIMAL to a shorter TIME

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (1.9999999);

ALTER TABLE t1 MODIFY a TIME(3);               -- Use this for MySQL

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 00:00:02
Oracle: ORA-01735: invalid ALTER TABLE option
PgSQL: error(wrong types)

ALTER TIMESTAMP(7)-alike-DECIMAL to a shorter TIMESTAMP

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (20010101235959.9999999);

ALTER TABLE t1 MODIFY a TIMESTAMP(3);               -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02 00:00:00
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: error(wrong types)

ALTER TIMESTAMP(7)-alike-DECIMAL to DATE

-- Oracle specific

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));

INSERT INTO t1 VALUES (20010101235959.9999999);

ALTER TABLE t1 MODIFY a DATE;                        -- Use this for MySQL, Oracle

-- ALTER TABLE t1 ALTER COLUMN a TYPE DATE;          -- Use this for PgSQL

SELECT a FROM t1;

MySQL: 2001-01-02
Oracle: ORA-01439: column to be modified must be empty to change datatype
PgSQL: error(wrong types)

Alexander Barkov added a comment - 2018-11-21 03:10 Details on how rounding works on ALTER in other databases ALTER from TIME to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a TIME (4)); INSERT INTO t1 VALUES ( '00:00:01.9999' ); ALTER TABLE t1 MODIFY a TIME (3); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A (No TIME data type) PgSQL: 00:00:02 ALTER TIMESTAMP to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (4)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999' ); ALTER TABLE t1 MODIFY a TIMESTAMP (3); -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision PgSQL: 2001-01-02 00:00:00 ALTER TIMESTAMP to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (4)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999' ); ALTER TABLE t1 MODIFY a DATE ; -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE DATE; -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 Oracle: 2001-01-02 PgSQL: 2001-01-01 ALTER from TIME(7)-in-VARCHAR to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '00:00:01.9999999' ); ALTER TABLE t1 MODIFY a TIME (3); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A PgSQL: column "a" cannot be cast automatically to type time without time zone DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '00:00:01.9999999' ); ALTER TABLE t1 MODIFY a TIME (6); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(6); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02.000000 Oracle: N/A PgSQL:" ERROR: column "a" cannot be cast automatically ALTER TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999999' ); ALTER TABLE t1 MODIFY a TIMESTAMP (3); -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: ERROR: column "a" cannot be cast automatically to type timestamp without time zone MySQL: 00:00:02.000000 Oracle: N/A PgSQL:" ERROR: column "a" cannot be cast automatically ALTER TIMESTAMP(7)-in-VARCHAR to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999999' ); ALTER TABLE t1 MODIFY a DATE ; -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE DATE; -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: ERROR: column "a" cannot be cast automatically to type date ALTER from TIME-alike-VARCHAR to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '00:00:01.9999' ); ALTER TABLE t1 MODIFY a TIME (3); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02 Oracle: N/A PgSQL: error (wrong types) ALTER TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999' ); ALTER TABLE t1 MODIFY a TIMESTAMP (3); -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: error (column must be empty PgSQL: error (wrong types) ALTER TIMESTAMP-alike-VARCHAR to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR (64)); INSERT INTO t1 VALUES ( '2001-01-01 23:59:59.9999' ); ALTER TABLE t1 MODIFY a DATE ; -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE DATE; -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 Oracle: error (column must be empty) PgSQL: error (wrong types) ALTER from TIME(4)-alike-DECIMAL to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (1.9999); ALTER TABLE t1 MODIFY a TIME (3); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02 Oracle: ORA-01735: invalid ALTER TABLE option PgSQL: error(wrong types) ALTER TIMESTAMP(4)-alike-DECIMAL to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (20010101235959.9999); ALTER TABLE t1 MODIFY a TIMESTAMP (3); -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: error(wrong types) ALTER TIMESTAMP(4)-alike-DECIMAL to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (20010101235959.9999); ALTER TABLE t1 MODIFY a DATE ; -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE DATE; -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: error(wrong types) ALTER from TIME(7)-alike-DECIMAL to a shorter TIME DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (1.9999999); ALTER TABLE t1 MODIFY a TIME (3); -- Use this for MySQL -- ALTER TABLE t1 ALTER COLUMN a TYPE TIME(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 00:00:02 Oracle: ORA-01735: invalid ALTER TABLE option PgSQL: error(wrong types) ALTER TIMESTAMP(7)-alike-DECIMAL to a shorter TIMESTAMP -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (20010101235959.9999999); ALTER TABLE t1 MODIFY a TIMESTAMP (3); -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE TIMESTAMP(3); -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 00:00:00 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: error(wrong types) ALTER TIMESTAMP(7)-alike-DECIMAL to DATE -- Oracle specific ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; DROP TABLE t1; CREATE TABLE t1 (a DECIMAL (38,10)); INSERT INTO t1 VALUES (20010101235959.9999999); ALTER TABLE t1 MODIFY a DATE ; -- Use this for MySQL, Oracle -- ALTER TABLE t1 ALTER COLUMN a TYPE DATE; -- Use this for PgSQL SELECT a FROM t1; MySQL: 2001-01-02 Oracle: ORA-01439: column to be modified must be empty to change datatype PgSQL: error(wrong types)

Alexander Barkov added a comment - 2018-11-21 03:10

Details on how rounding works on explicit CAST in other databases

Explicit CAST from a TIME(4) literal to TIME(3)

-- MySQL

SELECT CAST(TIME'00:00:01.9999' AS TIME(3));

-- PgSQL

SELECT CAST(TIME'00:00:01.9999' AS TIME(3));

MySQL: 00:00:02
Oracle: N/A
PgSQL: 00:00:02

Explicit CAST from a TIMESTAMP(4) literal to TIMESTAMP(3)

-- MySQL

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATETIME(3));

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS TIMESTAMP(3)) FROM DUAL;

-- PgSQL

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS TIMESTAMP(3));

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: 2001-01-02 00:00:00

Explicit CAST from a TIMESTAMP(4) literal to DATE

-- MySQL

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATE);

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATE) FROM DUAL;

-- PgSQL

SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATE);

MySQL: 2001-01-01
Oracle: 2001-01-01
PgSQL: 2001-01-01

Explicit CAST from a TIME(4)-in-string to TIME(3)

-- MySQL

SELECT CAST('00:00:01.9999' AS TIME(3));

-- PgSQL

SELECT CAST('00:00:01.9999' AS TIME(3));

MySQL: 00:00:02
Oracle: N/A
PgSQL: 00:00:02

Explicit CAST from a TIMESTAMP(4)-in-string to TIMESTAMP(3)

-- MySQL

SELECT CAST('2001-01-01 23:59:59.9999' AS DATETIME(3));

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST('2001-01-01 23:59:59.9999' AS TIMESTAMP(3)) FROM DUAL;

-- PgSQL

SELECT CAST('2001-01-01 23:59:59.9999' AS TIMESTAMP(3));

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: 2001-01-02 00:00:00

Explicit CAST from a TIMESTAMP(4)-in-string to DATE

-- MySQL

SELECT CAST('2001-01-01 23:59:59.9999' AS DATE);

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST('2001-01-01 23:59:59.9999' AS DATE) FROM DUAL;

-- PgSQL

SELECT CAST('2001-01-01 23:59:59.9999' AS DATE);

MySQL: 2001-01-01
Oracle: error (wrong format)
PgSQL: 2001-01-01

Explicit CAST from a TIME(7)-in-string to TIME(3)

-- MySQL

SELECT CAST('00:00:01.9999999' AS TIME(3));

-- PgSQL

SELECT CAST('00:00:01.9999999' AS TIME(3));

MySQL: 00:00:02
Oracle: N/A
PgSQL: 00:00:02

Explicit CAST from a TIMESTAMP(7)-in-string to TIMESTAMP(3)

-- MySQL

SELECT CAST('2001-01-01 23:59:59.9999999' AS DATETIME(3));

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST('2001-01-01 23:59:59.9999999' AS TIMESTAMP(3)) FROM DUAL;

-- PgSQL

SELECT CAST('2001-01-01 23:59:59.9999999' AS TIMESTAMP(3));

MySQL: 2001-01-02 00:00:00
Oracle: 2001-01-02 00:00:00.000
PgSQL: 2001-01-02 00:00:00

Explicit CAST from a TIMESTAMP(7)-in-string to DATE

-- MySQL

SELECT CAST('2001-01-01 23:59:59.9999999' AS DATE);

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST('2001-01-01 23:59:59.9999999' AS DATE) FROM DUAL;

-- PgSQL

SELECT CAST('2001-01-01 23:59:59.9999999' AS DATE);

MySQL: 2001-01-02
Oracle: error (wrong format)
PgSQL: 2001-01-01

Explicit CAST from a TIME(4)-in-decimal to TIME(3)

-- MySQL, Oracle

SELECT CAST(1.9999 AS TIME(3)) FROM DUAL;

-- PgSQL

SELECT CAST(1.9999 AS TIME(3));

MySQL: 00:00:02
Oracle: ORA-00932: inconsistent datatypes: expected TIME got NUMBER
PgSQL: error(wrong types)

Explicit CAST from a TIMESTAMP(4)-in-decimal to TIMESTAMP(3)

-- MySQL

SELECT CAST(20010101235959.9999 AS DATETIME(3));

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(20010101235959.9999 AS TIMESTAMP(3)) FROM DUAL;

-- PgSQL

SELECT CAST(20010101235959.9999 AS TIMESTAMP(3));

MySQL: 2001-01-02 00:00:00
Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
PgSQL: error(wrong types)

Explicit CAST from a TIMESTAMP(4)-in-decimal to DATE

-- MySQL

SELECT CAST(20010101235959.9999 AS DATE);

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(20010101235959.9999 AS DATE) FROM DUAL;

-- PgSQL

SELECT CAST(20010101235959.9999 AS DATE);

MySQL: 2001-01-01
Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
PgSQL: error(wrong types)

Explicit CAST from a TIME(7)-in-decimal to TIME(3)

-- MySQL

SELECT CAST(1.9999999 AS TIME(3)) FROM DUAL;

-- PgSQL

SELECT CAST(1.9999999 AS TIME(3));

MySQL: 00:00:02
Oracle: N/A
PgSQL: error(wrong types)

Explicit CAST from a TIMESTAMP(7)-in-decimal to TIMESTAMP(3)

-- MySQL

SELECT CAST(20010101235959.9999999 AS DATETIME(3));

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(20010101235959.9999999 AS TIMESTAMP(3)) FROM DUAL;

-- PgSQL

SELECT CAST(20010101235959.9999999 AS TIMESTAMP(3));

MySQL: 2001-01-02 00:00:00
Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
PgSQL: error(wrong types)

Explicit CAST from a TIMESTAMP(7)-in-decimal to DATE

-- MySQL

SELECT CAST(20010101235959.9999999 AS DATE);

-- Oracle

ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

SELECT CAST(20010101235959.9999999 AS DATE) FROM DUAL;

-- PgSQL

SELECT CAST(20010101235959.9999999 AS DATE);

MySQL: 2001-01-02
Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
PgSQL: error(wrong types)

Alexander Barkov added a comment - 2018-11-21 03:10 Details on how rounding works on explicit CAST in other databases Explicit CAST from a TIME(4) literal to TIME(3) -- MySQL SELECT CAST ( TIME '00:00:01.9999' AS TIME (3)); -- PgSQL SELECT CAST ( TIME '00:00:01.9999' AS TIME (3)); MySQL: 00:00:02 Oracle: N/A PgSQL: 00:00:02 Explicit CAST from a TIMESTAMP(4) literal to TIMESTAMP(3) -- MySQL SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS DATETIME(3)); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS TIMESTAMP (3)) FROM DUAL; -- PgSQL SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS TIMESTAMP (3)); MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: 2001-01-02 00:00:00 Explicit CAST from a TIMESTAMP(4) literal to DATE -- MySQL SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS DATE ); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS DATE ) FROM DUAL; -- PgSQL SELECT CAST ( TIMESTAMP '2001-01-01 23:59:59.9999' AS DATE ); MySQL: 2001-01-01 Oracle: 2001-01-01 PgSQL: 2001-01-01 Explicit CAST from a TIME(4)-in-string to TIME(3) -- MySQL SELECT CAST ( '00:00:01.9999' AS TIME (3)); -- PgSQL SELECT CAST ( '00:00:01.9999' AS TIME (3)); MySQL: 00:00:02 Oracle: N/A PgSQL: 00:00:02 Explicit CAST from a TIMESTAMP(4)-in-string to TIMESTAMP(3) -- MySQL SELECT CAST ( '2001-01-01 23:59:59.9999' AS DATETIME(3)); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( '2001-01-01 23:59:59.9999' AS TIMESTAMP (3)) FROM DUAL; -- PgSQL SELECT CAST ( '2001-01-01 23:59:59.9999' AS TIMESTAMP (3)); MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: 2001-01-02 00:00:00 Explicit CAST from a TIMESTAMP(4)-in-string to DATE -- MySQL SELECT CAST ( '2001-01-01 23:59:59.9999' AS DATE ); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( '2001-01-01 23:59:59.9999' AS DATE ) FROM DUAL; -- PgSQL SELECT CAST ( '2001-01-01 23:59:59.9999' AS DATE ); MySQL: 2001-01-01 Oracle: error (wrong format) PgSQL: 2001-01-01 Explicit CAST from a TIME(7)-in-string to TIME(3) -- MySQL SELECT CAST ( '00:00:01.9999999' AS TIME (3)); -- PgSQL SELECT CAST ( '00:00:01.9999999' AS TIME (3)); MySQL: 00:00:02 Oracle: N/A PgSQL: 00:00:02 Explicit CAST from a TIMESTAMP(7)-in-string to TIMESTAMP(3) -- MySQL SELECT CAST ( '2001-01-01 23:59:59.9999999' AS DATETIME(3)); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( '2001-01-01 23:59:59.9999999' AS TIMESTAMP (3)) FROM DUAL; -- PgSQL SELECT CAST ( '2001-01-01 23:59:59.9999999' AS TIMESTAMP (3)); MySQL: 2001-01-02 00:00:00 Oracle: 2001-01-02 00:00:00.000 PgSQL: 2001-01-02 00:00:00 Explicit CAST from a TIMESTAMP(7)-in-string to DATE -- MySQL SELECT CAST ( '2001-01-01 23:59:59.9999999' AS DATE ); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST ( '2001-01-01 23:59:59.9999999' AS DATE ) FROM DUAL; -- PgSQL SELECT CAST ( '2001-01-01 23:59:59.9999999' AS DATE ); MySQL: 2001-01-02 Oracle: error (wrong format) PgSQL: 2001-01-01 Explicit CAST from a TIME(4)-in-decimal to TIME(3) -- MySQL, Oracle SELECT CAST (1.9999 AS TIME (3)) FROM DUAL; -- PgSQL SELECT CAST (1.9999 AS TIME (3)); MySQL: 00:00:02 Oracle: ORA-00932: inconsistent datatypes: expected TIME got NUMBER PgSQL: error(wrong types) Explicit CAST from a TIMESTAMP(4)-in-decimal to TIMESTAMP(3) -- MySQL SELECT CAST (20010101235959.9999 AS DATETIME(3)); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST (20010101235959.9999 AS TIMESTAMP (3)) FROM DUAL; -- PgSQL SELECT CAST (20010101235959.9999 AS TIMESTAMP (3)); MySQL: 2001-01-02 00:00:00 Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER PgSQL: error(wrong types) Explicit CAST from a TIMESTAMP(4)-in-decimal to DATE -- MySQL SELECT CAST (20010101235959.9999 AS DATE ); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST (20010101235959.9999 AS DATE ) FROM DUAL; -- PgSQL SELECT CAST (20010101235959.9999 AS DATE ); MySQL: 2001-01-01 Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER PgSQL: error(wrong types) Explicit CAST from a TIME(7)-in-decimal to TIME(3) -- MySQL SELECT CAST (1.9999999 AS TIME (3)) FROM DUAL; -- PgSQL SELECT CAST (1.9999999 AS TIME (3)); MySQL: 00:00:02 Oracle: N/A PgSQL: error(wrong types) Explicit CAST from a TIMESTAMP(7)-in-decimal to TIMESTAMP(3) -- MySQL SELECT CAST (20010101235959.9999999 AS DATETIME(3)); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST (20010101235959.9999999 AS TIMESTAMP (3)) FROM DUAL; -- PgSQL SELECT CAST (20010101235959.9999999 AS TIMESTAMP (3)); MySQL: 2001-01-02 00:00:00 Oracle: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER PgSQL: error(wrong types) Explicit CAST from a TIMESTAMP(7)-in-decimal to DATE -- MySQL SELECT CAST (20010101235959.9999999 AS DATE ); -- Oracle ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' ; ALTER SESSION SET nls_date_format = 'yyyy-mm-dd' ; SELECT CAST (20010101235959.9999999 AS DATE ) FROM DUAL; -- PgSQL SELECT CAST (20010101235959.9999999 AS DATE ); MySQL: 2001-01-02 Oracle: ORA-00932: inconsistent datatypes: expected DATE got NUMBER PgSQL: error(wrong types)

Alexander Barkov added a comment - 2018-11-24 06:56

holyfoot, please review. The patch can be found in this branch on github:
https://github.com/MariaDB/server/commits/bb-10.4-mdev16991

Alexander Barkov added a comment - 2018-11-24 06:56 holyfoot , please review. The patch can be found in this branch on github: https://github.com/MariaDB/server/commits/bb-10.4-mdev16991

Alexander Barkov added a comment - 2018-11-25 05:45

Please find a new version in bb-10.4-mdev16991
(with suggestions to sql_basic_type.h addressed partially).

Alexander Barkov added a comment - 2018-11-25 05:45 Please find a new version in bb-10.4-mdev16991 (with suggestions to sql_basic_type.h addressed partially).

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-08-15 19:30

Updated:: 2020-11-28 11:51

Resolved:: 2018-11-26 11:14

Git Integration

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

MariaDB Server

Details

Description

MySQL compatibility

Oracle compatibility

Summary on data type conversion in other databases

Attachments

Issue Links

Activity

DATETIME to TIMESTAMP conversion with rounding in MySQL

Details on how various functions work in other databases

EXTRACT(SECOND)

EXTRACT(MINUTE)

EXTRACT(DAY)

MINUTE()

DAY()

TIME_TO_SEC()

Other functions with date input that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

Other functions with datetime/time input that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

Functions that construct TIME and round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

Other functions that round nanoseconds in MySQL

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

Other dyadic functions that round nanoseconds in MySQL for both arguments

MariaDB will also do rounding in these functions when TIME_ROUND_FRACTIONAL is set

FROM_UNIXTIME is questionable in MySQL.

MariaDB will round nanoseconds, but will return NULL instead of going out of the range:

STR_TO_DATE is questionable in MySQL

DATE_ADD is questionable in MySQL

Details on how rounding works on SET assignment in other databases

SET from TIME to a shorter TIME

SET from TIMESTAMP to a shorter TIMESTAMP

SET from TIMESTAMP to DATE

SET from TIME-alike-VARCHAR to a shorter TIME

SET from TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP

SET from TIMESTAMP-alike-VARCHAR to DATE

SET from TIME(7)-in-VARCHAR to a shorter TIME

SET from TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP

SET from TIMESTAMP(7)-in-VARCHAR to DATE

SET from TIME(4)-in-DECIMAL to a shorter TIME

SET from TIMESTAMP(4)-in-DECIMAL to a shorter TIMESTAMP

SET from TIMESTAMP(4)-in-DECIMAL to DATE

SET from TIME(7)-in-DECIMAL to a shorter TIME

SET from TIMESTAMP(7)-in-DECIMAL to a shorter TIMESTAMP

SET from TIMESTAMP(7)-in-DECIMAL to DATE

Details on how rounding works on ALTER in other databases

ALTER from TIME to a shorter TIME

ALTER TIMESTAMP to a shorter TIMESTAMP

ALTER TIMESTAMP to DATE

ALTER from TIME(7)-in-VARCHAR to a shorter TIME

ALTER TIMESTAMP(7)-in-VARCHAR to a shorter TIMESTAMP

ALTER TIMESTAMP(7)-in-VARCHAR to DATE

ALTER from TIME-alike-VARCHAR to a shorter TIME

ALTER TIMESTAMP-alike-VARCHAR to a shorter TIMESTAMP

ALTER TIMESTAMP-alike-VARCHAR to DATE

ALTER from TIME(4)-alike-DECIMAL to a shorter TIME

ALTER TIMESTAMP(4)-alike-DECIMAL to a shorter TIMESTAMP

ALTER TIMESTAMP(4)-alike-DECIMAL to DATE

ALTER from TIME(7)-alike-DECIMAL to a shorter TIME

ALTER TIMESTAMP(7)-alike-DECIMAL to a shorter TIMESTAMP

ALTER TIMESTAMP(7)-alike-DECIMAL to DATE

Details on how rounding works on explicit CAST in other databases

Explicit CAST from a TIME(4) literal to TIME(3)

Explicit CAST from a TIMESTAMP(4) literal to TIMESTAMP(3)

Explicit CAST from a TIMESTAMP(4) literal to DATE

Explicit CAST from a TIME(4)-in-string to TIME(3)

Explicit CAST from a TIMESTAMP(4)-in-string to TIMESTAMP(3)

Explicit CAST from a TIMESTAMP(4)-in-string to DATE

Explicit CAST from a TIME(7)-in-string to TIME(3)

Explicit CAST from a TIMESTAMP(7)-in-string to TIMESTAMP(3)

Explicit CAST from a TIMESTAMP(7)-in-string to DATE

Explicit CAST from a TIME(4)-in-decimal to TIME(3)

Explicit CAST from a TIMESTAMP(4)-in-decimal to TIMESTAMP(3)

Explicit CAST from a TIMESTAMP(4)-in-decimal to DATE

Explicit CAST from a TIME(7)-in-decimal to TIME(3)

Explicit CAST from a TIMESTAMP(7)-in-decimal to TIMESTAMP(3)

Explicit CAST from a TIMESTAMP(7)-in-decimal to DATE

People

MariaDB will round nanoseconds, but will return `NULL` instead of going out of the range: