DROP TABLE IF EXISTS t1;
set @save_time_zone=@@time_zone;
set time_zone='UTC';
CREATE TABLE t1 (
d DATE ,
dt DATETIME ,
ts TIMESTAMP ,
t TIME ,
y YEAR ,
y4 YEAR(4) ,
y2 YEAR(2) ,
pk DATETIME PRIMARY KEY
) ENGINE=rocksdb;
Warnings:
Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
d	date	YES		NULL	
dt	datetime	YES		NULL	
ts	timestamp	YES		NULL	
t	time	YES		NULL	
y	year(4)	YES		NULL	
y4	year(4)	YES		NULL	
y2	year(2)	YES		NULL	
pk	datetime	NO	PRI	NULL	
SET @tm = '2012-04-09 05:27:00';
INSERT INTO t1 (d,dt,ts,t,y,y4,y2,pk) VALUES
('1000-01-01', '1000-01-01 00:00:00', FROM_UNIXTIME(1), '-838:59:59', '1901', '1901', '00','2012-12-12 12:12:12'),
('9999-12-31', '9999-12-31 23:59:59', FROM_UNIXTIME(2147483647), '838:59:59', '2155', '2155', '99','2012-12-12 12:12:13'),
('0000-00-00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '00:00:00', '0', '0', '0','2012-12-12 12:12:14'),
(DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),YEAR(@tm),YEAR(@tm),'2012-12-12 12:12:15');
SELECT d,dt,ts,t,y,y4,y2 FROM t1;
d	dt	ts	t	y	y4	y2
0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00	00:00:00	2000	2000	00
1000-01-01	1000-01-01 00:00:00	1970-01-01 00:00:01	-838:59:59	1901	1901	00
2012-04-09	2012-04-09 05:27:00	2012-04-09 05:27:00	05:27:00	2012	2012	12
9999-12-31	9999-12-31 23:59:59	2038-01-19 03:14:07	838:59:59	2155	2155	99
INSERT INTO t1 (d,dt,ts,t,y,y4,y2,pk) VALUES
('999-13-32', '999-11-31 00:00:00', '0', '-839:00:00', '1900', '1900', '-1','2012-12-12 12:12:16');
Warnings:
Warning	1265	Data truncated for column 'd' at row 1
Warning	1265	Data truncated for column 'dt' at row 1
Warning	1265	Data truncated for column 'ts' at row 1
Warning	1264	Out of range value for column 't' at row 1
Warning	1264	Out of range value for column 'y' at row 1
Warning	1264	Out of range value for column 'y4' at row 1
Warning	1264	Out of range value for column 'y2' at row 1
SELECT d,dt,ts,t,y,y4,y2 FROM t1;
d	dt	ts	t	y	y4	y2
1000-01-01	1000-01-01 00:00:00	1970-01-01 00:00:01	-838:59:59	1901	1901	00
9999-12-31	9999-12-31 23:59:59	2038-01-19 03:14:07	838:59:59	2155	2155	99
0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00	00:00:00	2000	2000	00
2012-04-09	2012-04-09 05:27:00	2012-04-09 05:27:00	05:27:00	2012	2012	12
0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00	-838:59:59	0000	0000	00
set time_zone=@save_time_zone;
DROP TABLE t1;