1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
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;
|