database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-11-03 17:13:17 +03:00
Code Activity
Files
cmapi-json-in-out-by-default
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/mcs92_date_functions.result
Serguey Zefirov 39a976c39a fix(ubsan): MCOL-5844 - iron out UBSAN reports 
The most important fix here is the fix of possible buffer overrun in
DATEFORMAT() function. A "%W" format, repeated enough times, would
overflow the 256-bytes buffer for result. Now we use ostringstream to
construct result and we are safe.

Changes in date/time projection functions made me fix difference between
us and server behavior. The new, better behavior is reflected in changes
in tests' results.

Also, there was incorrect logic in TRUNCATE() and ROUND() functions in
computing the decimal "shift."
2024-12-10 20:30:58 +04:00

411 lines
16 KiB
Plaintext
Raw Permalink Blame History

DROP DATABASE IF EXISTS mcs92_db;
CREATE DATABASE mcs92_db;
USE mcs92_db;
CREATE TABLE t1(t1_d DATE, t1_t TIME, t1_dt DATETIME)ENGINE=Columnstore;
INSERT INTO t1 VALUES(NULL, NULL, NULL);
INSERT INTO t1 VALUES('0000-00-00', '00:00:00', '0000-00-00');
INSERT INTO t1 VALUES('2222-11-11', '12:12:12', '2020-11-11 12:12:12');
INSERT INTO t1 VALUES('2020-01-01', '11:11:11', '2020-12-31 12:34:56');
INSERT INTO t1 VALUES('2016-02-01', '11:11:11', '2017-02-01 12:12:12');
SELECT t1_d, t1_t, t1_dt, DATEDIFF(t1_dt, t1_d) FROM t1;
t1_d t1_t t1_dt DATEDIFF(t1_dt, t1_d)
NULL NULL NULL NULL
0000-00-00 00:00:00 0000-00-00 00:00:00 0
2222-11-11 12:12:12 2020-11-11 12:12:12 -73778
2020-01-01 11:11:11 2020-12-31 12:34:56 365
2016-02-01 11:11:11 2017-02-01 12:12:12 366
SELECT t1_d, t1_t, t1_dt, DATEDIFF(t1_dt, t1_d) "Datediff in days" FROM t1 WHERE t1_dt > 0;
t1_d t1_t t1_dt Datediff in days
2222-11-11 12:12:12 2020-11-11 12:12:12 -73778
2020-01-01 11:11:11 2020-12-31 12:34:56 365
2016-02-01 11:11:11 2017-02-01 12:12:12 366
SELECT t1_d, t1_t, t1_dt, DAY(t1_dt), DAY(t1_d) FROM t1;
t1_d t1_t t1_dt DAY(t1_dt) DAY(t1_d)
NULL NULL NULL NULL NULL
0000-00-00 00:00:00 0000-00-00 00:00:00 0 0
2222-11-11 12:12:12 2020-11-11 12:12:12 11 11
2020-01-01 11:11:11 2020-12-31 12:34:56 31 1
2016-02-01 11:11:11 2017-02-01 12:12:12 1 1
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL 1 SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL 1 SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:13
2020-12-31 12:34:56 2020-12-31 12:34:57
2017-02-01 12:12:12 2017-02-01 12:12:13
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL -1 SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL -1 SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:11
2020-12-31 12:34:56 2020-12-31 12:34:55
2017-02-01 12:12:12 2017-02-01 12:12:11
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL 1 DAY) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL 1 DAY)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 12:12:12
2020-12-31 12:34:56 2021-01-01 12:34:56
2017-02-01 12:12:12 2017-02-02 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL -1 DAY) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL -1 DAY)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 12:12:12
2020-12-31 12:34:56 2020-12-30 12:34:56
2017-02-01 12:12:12 2017-01-31 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL 1 MONTH) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL 1 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-12-11 12:12:12
2020-12-31 12:34:56 2021-01-31 12:34:56
2017-02-01 12:12:12 2017-03-01 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL -1 MONTH) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL -1 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-10-11 12:12:12
2020-12-31 12:34:56 2020-11-30 12:34:56
2017-02-01 12:12:12 2017-01-01 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL 0 MONTH) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL 0 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:12
2020-12-31 12:34:56 2020-12-31 12:34:56
2017-02-01 12:12:12 2017-02-01 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '2:1' MINUTE_SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '2:1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:14:13
2020-12-31 12:34:56 2020-12-31 12:36:57
2017-02-01 12:12:12 2017-02-01 12:14:13
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '-2:1' MINUTE_SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '-2:1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:10:13
2020-12-31 12:34:56 2020-12-31 12:32:57
2017-02-01 12:12:12 2017-02-01 12:10:13
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '2:-1' MINUTE_SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '2:-1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:14:12
2020-12-31 12:34:56 2020-12-31 12:36:56
2017-02-01 12:12:12 2017-02-01 12:14:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '-2:-1' MINUTE_SECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '-2:-1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:10:12
2020-12-31 12:34:56 2020-12-31 12:32:56
2017-02-01 12:12:12 2017-02-01 12:10:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '-1 2' DAY_HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '-1 2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 14:12:12
2020-12-31 12:34:56 2020-12-30 14:34:56
2017-02-01 12:12:12 2017-01-31 14:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '1 2' DAY_HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '1 2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 14:12:12
2020-12-31 12:34:56 2021-01-01 14:34:56
2017-02-01 12:12:12 2017-02-02 14:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '-1 -2' DAY_HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '-1 -2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 12:12:12
2020-12-31 12:34:56 2020-12-30 12:34:56
2017-02-01 12:12:12 2017-01-31 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '1 -2' DAY_HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '1 -2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 12:12:12
2020-12-31 12:34:56 2021-01-01 12:34:56
2017-02-01 12:12:12 2017-02-02 12:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '1.9' SECOND_MICROSECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '1.9' SECOND_MICROSECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:13.900000
2020-12-31 12:34:56 2020-12-31 12:34:57.900000
2017-02-01 12:12:12 2017-02-01 12:12:13.900000
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL '-1.9' SECOND_MICROSECOND) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL '-1.9' SECOND_MICROSECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:11.900000
2020-12-31 12:34:56 2020-12-31 12:34:55.900000
2017-02-01 12:12:12 2017-02-01 12:12:11.900000
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL 12 HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL 12 HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 00:12:12
2020-12-31 12:34:56 2021-01-01 00:34:56
2017-02-01 12:12:12 2017-02-02 00:12:12
SELECT t1_dt, DATE_ADD(t1_dt, INTERVAL -1 HOUR) FROM t1;
t1_dt DATE_ADD(t1_dt, INTERVAL -1 HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 11:12:12
2020-12-31 12:34:56 2020-12-31 11:34:56
2017-02-01 12:12:12 2017-02-01 11:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL 1 SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL 1 SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:11
2020-12-31 12:34:56 2020-12-31 12:34:55
2017-02-01 12:12:12 2017-02-01 12:12:11
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL -1 SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL -1 SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:13
2020-12-31 12:34:56 2020-12-31 12:34:57
2017-02-01 12:12:12 2017-02-01 12:12:13
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL 1 DAY) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL 1 DAY)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 12:12:12
2020-12-31 12:34:56 2020-12-30 12:34:56
2017-02-01 12:12:12 2017-01-31 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL -1 DAY) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL -1 DAY)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 12:12:12
2020-12-31 12:34:56 2021-01-01 12:34:56
2017-02-01 12:12:12 2017-02-02 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL 1 MONTH) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL 1 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-10-11 12:12:12
2020-12-31 12:34:56 2020-11-30 12:34:56
2017-02-01 12:12:12 2017-01-01 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL -1 MONTH) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL -1 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-12-11 12:12:12
2020-12-31 12:34:56 2021-01-31 12:34:56
2017-02-01 12:12:12 2017-03-01 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL 0 MONTH) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL 0 MONTH)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:12
2020-12-31 12:34:56 2020-12-31 12:34:56
2017-02-01 12:12:12 2017-02-01 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '2:1' MINUTE_SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '2:1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:10:11
2020-12-31 12:34:56 2020-12-31 12:32:55
2017-02-01 12:12:12 2017-02-01 12:10:11
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '-2:1' MINUTE_SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '-2:1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:14:11
2020-12-31 12:34:56 2020-12-31 12:36:55
2017-02-01 12:12:12 2017-02-01 12:14:11
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '2:-1' MINUTE_SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '2:-1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:10:12
2020-12-31 12:34:56 2020-12-31 12:32:56
2017-02-01 12:12:12 2017-02-01 12:10:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '-2:-1' MINUTE_SECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '-2:-1' MINUTE_SECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:14:12
2020-12-31 12:34:56 2020-12-31 12:36:56
2017-02-01 12:12:12 2017-02-01 12:14:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '-1 2' DAY_HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '-1 2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 10:12:12
2020-12-31 12:34:56 2021-01-01 10:34:56
2017-02-01 12:12:12 2017-02-02 10:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '1 2' DAY_HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '1 2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 10:12:12
2020-12-31 12:34:56 2020-12-30 10:34:56
2017-02-01 12:12:12 2017-01-31 10:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '-1 -2' DAY_HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '-1 -2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-12 12:12:12
2020-12-31 12:34:56 2021-01-01 12:34:56
2017-02-01 12:12:12 2017-02-02 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '1 -2' DAY_HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '1 -2' DAY_HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-10 12:12:12
2020-12-31 12:34:56 2020-12-30 12:34:56
2017-02-01 12:12:12 2017-01-31 12:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '1.9' SECOND_MICROSECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '1.9' SECOND_MICROSECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:10.999991
2020-12-31 12:34:56 2020-12-31 12:34:54.999991
2017-02-01 12:12:12 2017-02-01 12:12:10.999991
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL '-1.9' SECOND_MICROSECOND) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL '-1.9' SECOND_MICROSECOND)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 12:12:12.999991
2020-12-31 12:34:56 2020-12-31 12:34:56.999991
2017-02-01 12:12:12 2017-02-01 12:12:12.999991
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL 12 HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL 12 HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 00:12:12
2020-12-31 12:34:56 2020-12-31 00:34:56
2017-02-01 12:12:12 2017-02-01 00:12:12
SELECT t1_dt, DATE_SUB(t1_dt, INTERVAL -1 HOUR) FROM t1;
t1_dt DATE_SUB(t1_dt, INTERVAL -1 HOUR)
NULL NULL
0000-00-00 00:00:00 NULL
2020-11-11 12:12:12 2020-11-11 13:12:12
2020-12-31 12:34:56 2020-12-31 13:34:56
2017-02-01 12:12:12 2017-02-01 13:12:12
SELECT t1_dt, DATE_FORMAT(t1_dt, '%Y-%m-%d') a, DATE_FORMAT(t1_d, '%a %D %b %Y') b, DATE_FORMAT(t1_d, '%W %D %M %Y') c FROM t1;
t1_dt a b c
NULL NULL NULL NULL
0000-00-00 00:00:00 0000-00-00 NULL NULL
2020-11-11 12:12:12 2020-11-11 Mon 11th Nov 2222 Monday 11th November 2222
2020-12-31 12:34:56 2020-12-31 Wed 1st Jan 2020 Wednesday 1st January 2020
2017-02-01 12:12:12 2017-02-01 Mon 1st Feb 2016 Monday 1st February 2016
SELECT t1_dt, DATE_FORMAT(t1_dt, '%Y/%m/%d %T') a, DATE_FORMAT(t1_dt, '%a %D %b %Y %H:%i') b, DATE_FORMAT(t1_dt, '%W %D %M %Y %T') c FROM t1;
t1_dt a b c
NULL NULL NULL NULL
0000-00-00 00:00:00 0000/00/00 00:00:00 NULL NULL
2020-11-11 12:12:12 2020/11/11 12:12:12 Wed 11th Nov 2020 12:12 Wednesday 11th November 2020 12:12:12
2020-12-31 12:34:56 2020/12/31 12:34:56 Thu 31st Dec 2020 12:34 Thursday 31st December 2020 12:34:56
2017-02-01 12:12:12 2017/02/01 12:12:12 Wed 1st Feb 2017 12:12 Wednesday 1st February 2017 12:12:12
SELECT @@lc_time_names;
@@lc_time_names
en_US
SELECT t1_dt, DAYNAME(t1_dt), DAYOFWEEK(t1_dt), WEEKDAY(t1_dt) FROM t1;
t1_dt DAYNAME(t1_dt) DAYOFWEEK(t1_dt) WEEKDAY(t1_dt)
NULL NULL NULL NULL
0000-00-00 00:00:00 NULL NULL NULL
2020-11-11 12:12:12 Wednesday 4 2
2020-12-31 12:34:56 Thursday 5 3
2017-02-01 12:12:12 Wednesday 4 2
SELECT t1_d, DAYNAME(t1_d), DAYOFWEEK(t1_d), WEEKDAY(t1_d) FROM t1;
t1_d DAYNAME(t1_d) DAYOFWEEK(t1_d) WEEKDAY(t1_d)
NULL NULL NULL NULL
0000-00-00 NULL NULL NULL
2222-11-11 Monday 2 0
2020-01-01 Wednesday 4 2
2016-02-01 Monday 2 0
SELECT t1_dt, EXTRACT(SECOND FROM t1_dt) 'SECOND', EXTRACT(DAY_HOUR FROM t1_dt) 'DAY_HOUR', EXTRACT(HOUR FROM t1_dt) 'HOUR', EXTRACT(MINUTE FROM t1_dt) 'MINUTE' FROM t1;
t1_dt SECOND DAY_HOUR HOUR MINUTE
NULL NULL NULL NULL NULL
0000-00-00 00:00:00 0 0 0 0
2020-11-11 12:12:12 12 1112 12 12
2020-12-31 12:34:56 56 3112 12 34
2017-02-01 12:12:12 12 112 12 12
SELECT t1_dt, EXTRACT(DAY FROM t1_dt) 'DAY', EXTRACT(YEAR FROM t1_dt) 'YEAR', EXTRACT(MONTH FROM t1_dt) 'MONTH', EXTRACT(WEEK FROM t1_dt) 'WEEK' FROM t1;
t1_dt DAY YEAR MONTH WEEK
NULL NULL NULL NULL NULL
0000-00-00 00:00:00 0 0 0 1
2020-11-11 12:12:12 11 2020 11 46
2020-12-31 12:34:56 31 2020 12 53
2017-02-01 12:12:12 1 2017 2 5
SELECT t1_dt, MONTH(t1_dt) FROM t1;
t1_dt MONTH(t1_dt)
NULL NULL
0000-00-00 00:00:00 0
2020-11-11 12:12:12 11
2020-12-31 12:34:56 12
2017-02-01 12:12:12 2
SELECT t1_dt, t1_t, TIMEDIFF(t1_dt, t1_t) FROM t1;
t1_dt t1_t TIMEDIFF(t1_dt, t1_t)
NULL NULL NULL
0000-00-00 00:00:00 00:00:00 NULL
2020-11-11 12:12:12 12:12:12 NULL
2020-12-31 12:34:56 11:11:11 NULL
2017-02-01 12:12:12 11:11:11 NULL
SELECT t1_dt, TIMEDIFF(t1_dt, '5555-11-11 12:58:11') FROM t1;
t1_dt TIMEDIFF(t1_dt, '5555-11-11 12:58:11')
NULL NULL
0000-00-00 00:00:00 -838:59:59
2020-11-11 12:12:12 -838:59:59
2020-12-31 12:34:56 -838:59:59
2017-02-01 12:12:12 -838:59:59
SELECT t1_t, TIMEDIFF('12:58:11', t1_t) FROM t1;
t1_t TIMEDIFF('12:58:11', t1_t)
NULL NULL
00:00:00 12:58:11
12:12:12 00:45:59
11:11:11 01:47:00
11:11:11 01:47:00
SELECT t1_d, t1_dt, TIMESTAMPDIFF(MONTH, t1_d, t1_dt) FROM t1;
t1_d t1_dt TIMESTAMPDIFF(MONTH, t1_d, t1_dt)
NULL NULL NULL
0000-00-00 0000-00-00 00:00:00 0
2222-11-11 2020-11-11 12:12:12 -2424
2020-01-01 2020-12-31 12:34:56 11
2016-02-01 2017-02-01 12:12:12 12
SELECT t1_d, t1_dt, TIMESTAMPDIFF(DAY, t1_d, t1_dt) FROM t1;
t1_d t1_dt TIMESTAMPDIFF(DAY, t1_d, t1_dt)
NULL NULL NULL
0000-00-00 0000-00-00 00:00:00 0
2222-11-11 2020-11-11 12:12:12 -73777
2020-01-01 2020-12-31 12:34:56 365
2016-02-01 2017-02-01 12:12:12 366
SELECT t1_d, t1_dt, TIMESTAMPDIFF(MINUTE, t1_d, t1_dt) FROM t1;
t1_d t1_dt TIMESTAMPDIFF(MINUTE, t1_d, t1_dt)
NULL NULL NULL
0000-00-00 0000-00-00 00:00:00 0
2222-11-11 2020-11-11 12:12:12 -106239587
2020-01-01 2020-12-31 12:34:56 526354
2016-02-01 2017-02-01 12:12:12 527772
SELECT t1_d, t1_dt, TIMESTAMPDIFF(SECOND, t1_d, t1_dt) FROM t1;
t1_d t1_dt TIMESTAMPDIFF(SECOND, t1_d, t1_dt)
NULL NULL NULL
0000-00-00 0000-00-00 00:00:00 0
2222-11-11 2020-11-11 12:12:12 -6374375268
2020-01-01 2020-12-31 12:34:56 31581296
2016-02-01 2017-02-01 12:12:12 31666332
SELECT t1_d, WEEK(t1_d), t1_dt, WEEK(t1_dt) FROM t1;
t1_d WEEK(t1_d) t1_dt WEEK(t1_dt)
NULL NULL NULL NULL
0000-00-00 1 0000-00-00 00:00:00 1
2222-11-11 45 2020-11-11 12:12:12 45
2020-01-01 0 2020-12-31 12:34:56 52
2016-02-01 5 2017-02-01 12:12:12 5
SELECT t1_d, YEAR(t1_d), t1_dt, YEAR(t1_dt) FROM t1;
t1_d YEAR(t1_d) t1_dt YEAR(t1_dt)
NULL NULL NULL NULL
0000-00-00 0 0000-00-00 00:00:00 0
2222-11-11 2222 2020-11-11 12:12:12 2020
2020-01-01 2020 2020-12-31 12:34:56 2020
2016-02-01 2016 2017-02-01 12:12:12 2017
DROP DATABASE mcs92_db;
View Git Blame Copy Permalink