DROP DATABASE IF EXISTS mcs236_db;
CREATE DATABASE mcs236_db;
USE mcs236_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('1177-01-21', '12:12:12.282', '2020-11-11 12:12:12');
INSERT INTO t1 VALUES('2020-01-01', '11:11:11', '1561-12-31 12:34:56');
INSERT INTO t1 VALUES('2016-02-01', '11:11:11', '2017-02-01 12:12:12.07109');
SELECT t1_d, EXTRACT(MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(MICROSECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(SECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(MINUTE FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(HOUR FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(HOUR FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(DAY FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(DAY FROM t1_d)
NULL	NULL
1177-01-21	21
2016-02-01	1
2020-01-01	1
SELECT t1_d, EXTRACT(WEEK FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(WEEK FROM t1_d)
NULL	NULL
1177-01-21	3
2016-02-01	5
2020-01-01	1
SELECT t1_d, EXTRACT(MONTH FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(MONTH FROM t1_d)
NULL	NULL
1177-01-21	1
2016-02-01	2
2020-01-01	1
SELECT t1_d, EXTRACT(QUARTER FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(QUARTER FROM t1_d)
NULL	NULL
1177-01-21	1
2016-02-01	1
2020-01-01	1
SELECT t1_d, EXTRACT(YEAR FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(YEAR FROM t1_d)
NULL	NULL
1177-01-21	1177
2016-02-01	2016
2020-01-01	2020
SELECT t1_d, EXTRACT(SECOND_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(SECOND_MICROSECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(MINUTE_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(MINUTE_MICROSECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(MINUTE_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(MINUTE_SECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(HOUR_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(HOUR_MICROSECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(HOUR_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(HOUR_SECOND FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(HOUR_MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(HOUR_MINUTE FROM t1_d)
NULL	NULL
1177-01-21	0
2016-02-01	0
2020-01-01	0
SELECT t1_d, EXTRACT(DAY_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(DAY_MICROSECOND FROM t1_d)
NULL	NULL
1177-01-21	21000000000000
2016-02-01	1000000000000
2020-01-01	1000000000000
SELECT t1_d, EXTRACT(DAY_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(DAY_SECOND FROM t1_d)
NULL	NULL
1177-01-21	21000000
2016-02-01	1000000
2020-01-01	1000000
SELECT t1_d, EXTRACT(DAY_MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(DAY_MINUTE FROM t1_d)
NULL	NULL
1177-01-21	210000
2016-02-01	10000
2020-01-01	10000
SELECT t1_d, EXTRACT(DAY_HOUR FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(DAY_HOUR FROM t1_d)
NULL	NULL
1177-01-21	2100
2016-02-01	100
2020-01-01	100
SELECT t1_d, EXTRACT(YEAR_MONTH FROM t1_d) FROM t1 ORDER BY 1;
t1_d	EXTRACT(YEAR_MONTH FROM t1_d)
NULL	NULL
1177-01-21	117701
2016-02-01	201602
2020-01-01	202001
SELECT t1_dt, EXTRACT(MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(MICROSECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	0
2017-02-01 12:12:12	0
2020-11-11 12:12:12	0
SELECT t1_dt, EXTRACT(SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(SECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	56
2017-02-01 12:12:12	12
2020-11-11 12:12:12	12
SELECT t1_dt, EXTRACT(MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(MINUTE FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	34
2017-02-01 12:12:12	12
2020-11-11 12:12:12	12
SELECT t1_dt, EXTRACT(HOUR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(HOUR FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	12
2017-02-01 12:12:12	12
2020-11-11 12:12:12	12
SELECT t1_dt, EXTRACT(DAY FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(DAY FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	31
2017-02-01 12:12:12	1
2020-11-11 12:12:12	11
SELECT t1_dt, EXTRACT(WEEK FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(WEEK FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	53
2017-02-01 12:12:12	5
2020-11-11 12:12:12	46
SELECT t1_dt, EXTRACT(MONTH FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(MONTH FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	12
2017-02-01 12:12:12	2
2020-11-11 12:12:12	11
SELECT t1_dt, EXTRACT(QUARTER FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(QUARTER FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	4
2017-02-01 12:12:12	1
2020-11-11 12:12:12	4
SELECT t1_dt, EXTRACT(YEAR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(YEAR FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	1561
2017-02-01 12:12:12	2017
2020-11-11 12:12:12	2020
SELECT t1_dt, EXTRACT(SECOND_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(SECOND_MICROSECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	56000000
2017-02-01 12:12:12	12000000
2020-11-11 12:12:12	12000000
SELECT t1_dt, EXTRACT(MINUTE_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(MINUTE_MICROSECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	3456000000
2017-02-01 12:12:12	1212000000
2020-11-11 12:12:12	1212000000
SELECT t1_dt, EXTRACT(MINUTE_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(MINUTE_SECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	3456
2017-02-01 12:12:12	1212
2020-11-11 12:12:12	1212
SELECT t1_dt, EXTRACT(HOUR_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(HOUR_MICROSECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	123456000000
2017-02-01 12:12:12	121212000000
2020-11-11 12:12:12	121212000000
SELECT t1_dt, EXTRACT(HOUR_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(HOUR_SECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	123456
2017-02-01 12:12:12	121212
2020-11-11 12:12:12	121212
SELECT t1_dt, EXTRACT(HOUR_MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(HOUR_MINUTE FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	1234
2017-02-01 12:12:12	1212
2020-11-11 12:12:12	1212
SELECT t1_dt, EXTRACT(DAY_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(DAY_MICROSECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	31123456000000
2017-02-01 12:12:12	1121212000000
2020-11-11 12:12:12	11121212000000
SELECT t1_dt, EXTRACT(DAY_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(DAY_SECOND FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	31123456
2017-02-01 12:12:12	1121212
2020-11-11 12:12:12	11121212
SELECT t1_dt, EXTRACT(DAY_MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(DAY_MINUTE FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	311234
2017-02-01 12:12:12	11212
2020-11-11 12:12:12	111212
SELECT t1_dt, EXTRACT(DAY_HOUR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(DAY_HOUR FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	3112
2017-02-01 12:12:12	112
2020-11-11 12:12:12	1112
SELECT t1_dt, EXTRACT(YEAR_MONTH FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt	EXTRACT(YEAR_MONTH FROM t1_dt)
NULL	NULL
1561-12-31 12:34:56	156112
2017-02-01 12:12:12	201702
2020-11-11 12:12:12	202011
SELECT t1_t, EXTRACT(MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(MICROSECOND FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(SECOND FROM t1_t)
NULL	NULL
11:11:11	11
11:11:11	11
12:12:12	12
SELECT t1_t, EXTRACT(MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(MINUTE FROM t1_t)
NULL	NULL
11:11:11	11
11:11:11	11
12:12:12	12
SELECT t1_t, EXTRACT(HOUR FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(HOUR FROM t1_t)
NULL	NULL
11:11:11	11
11:11:11	11
12:12:12	12
SELECT t1_t, EXTRACT(DAY FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(DAY FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(WEEK FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(WEEK FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(MONTH FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(MONTH FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(QUARTER FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(QUARTER FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(YEAR FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(YEAR FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
SELECT t1_t, EXTRACT(SECOND_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(SECOND_MICROSECOND FROM t1_t)
NULL	NULL
11:11:11	11000000
11:11:11	11000000
12:12:12	12000000
SELECT t1_t, EXTRACT(MINUTE_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(MINUTE_MICROSECOND FROM t1_t)
NULL	NULL
11:11:11	1111000000
11:11:11	1111000000
12:12:12	1212000000
SELECT t1_t, EXTRACT(MINUTE_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(MINUTE_SECOND FROM t1_t)
NULL	NULL
11:11:11	1111
11:11:11	1111
12:12:12	1212
SELECT t1_t, EXTRACT(HOUR_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(HOUR_MICROSECOND FROM t1_t)
NULL	NULL
11:11:11	111111000000
11:11:11	111111000000
12:12:12	121212000000
SELECT t1_t, EXTRACT(HOUR_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(HOUR_SECOND FROM t1_t)
NULL	NULL
11:11:11	111111
11:11:11	111111
12:12:12	121212
SELECT t1_t, EXTRACT(HOUR_MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(HOUR_MINUTE FROM t1_t)
NULL	NULL
11:11:11	1111
11:11:11	1111
12:12:12	1212
SELECT t1_t, EXTRACT(DAY_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(DAY_MICROSECOND FROM t1_t)
NULL	NULL
11:11:11	111111000000
11:11:11	111111000000
12:12:12	121212000000
SELECT t1_t, EXTRACT(DAY_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(DAY_SECOND FROM t1_t)
NULL	NULL
11:11:11	111111
11:11:11	111111
12:12:12	121212
SELECT t1_t, EXTRACT(DAY_MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(DAY_MINUTE FROM t1_t)
NULL	NULL
11:11:11	1111
11:11:11	1111
12:12:12	1212
SELECT t1_t, EXTRACT(DAY_HOUR FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(DAY_HOUR FROM t1_t)
NULL	NULL
11:11:11	11
11:11:11	11
12:12:12	12
SELECT t1_t, EXTRACT(YEAR_MONTH FROM t1_t) FROM t1 ORDER BY 1;
t1_t	EXTRACT(YEAR_MONTH FROM t1_t)
NULL	NULL
11:11:11	0
11:11:11	0
12:12:12	0
DROP DATABASE mcs236_db;