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).
|
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:
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:
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:
|
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
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.
|
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;
|
|
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)
|
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)
|