mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-18 21:44:02 +03:00
0bc384d5f0
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."
411 lines
16 KiB
Plaintext
411 lines
16 KiB
Plaintext
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;
|