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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
#
# Test dates close to upper range
#
set time_zone="+03:00";
select from_unixtime(2147483648);
from_unixtime(2147483648)
NULL
Warnings:
Warning 1292 Truncated incorrect unixtime value: '2147483648'
select unix_timestamp(from_unixtime(2147483648));
unix_timestamp(from_unixtime(2147483648))
NULL
Warnings:
Warning 1292 Truncated incorrect unixtime value: '2147483648'
# bad year
select unix_timestamp('2039-01-20 01:00:00');
unix_timestamp('2039-01-20 01:00:00')
NULL
# bad month
select unix_timestamp('2038-02-10 01:00:00');
unix_timestamp('2038-02-10 01:00:00')
NULL
# bad day
select unix_timestamp('2038-01-20 01:00:00');
unix_timestamp('2038-01-20 01:00:00')
NULL
# check bad date, close to the boundary (we cut them off in the very end)
select unix_timestamp('2038-01-19 07:14:07');
unix_timestamp('2038-01-19 07:14:07')
NULL
set time_zone=MET;
select unix_timestamp('2038-01-19 04:14:07'),
unix_timestamp('2038-01-19 04:14:08');
unix_timestamp('2038-01-19 04:14:07') unix_timestamp('2038-01-19 04:14:08')
2147483647 NULL
set time_zone= @@global.time_zone;
#
# Functions that construct DATETIME
#
SET time_zone='+00:00';
SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
CREATE TABLE t1 (id SERIAL, a DECIMAL(30,10));
INSERT INTO t1 (a) VALUES (2147483647.9999999);
SELECT a, FROM_UNIXTIME(a) FROM t1 ORDER BY id;
a FROM_UNIXTIME(a)
2147483647.9999999000 NULL
DROP TABLE t1;
set time_zone= @@global.time_zone;
set sql_mode=default;
#
# Corner case:
# ALTER TIMESTAMP to a shorter TIMESTAMP
# All values round, maximum possible value truncates.
#
SET time_zone='+00:00';
SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
CREATE TABLE t1 (ID INT, a TIMESTAMP(6), comment VARCHAR(64));
INSERT INTO t1 VALUES (0, '2038-01-18 23:59:59.999999', 'Should round');
INSERT INTO t1 VALUES (1, '2038-01-19 03:14:06.999999', 'Should round');
INSERT INTO t1 VALUES (2, '2038-01-19 03:14:07.999999', 'Should truncate');
ALTER TABLE t1 MODIFY a TIMESTAMP(5);
Warnings:
Warning 1264 Out of range value for column 'a' at row 3
SELECT * FROM t1;
ID a comment
0 2038-01-19 00:00:00.00000 Should round
1 2038-01-19 03:14:07.00000 Should round
2 2038-01-19 03:14:07.99999 Should truncate
DROP TABLE t1;
set time_zone= @@global.time_zone;
set sql_mode=default;
# Let us test range for TIMESTAMP
#
create table t1 (ts timestamp);
set time_zone='UTC';
insert into t1 values ('2038-01-19 03:14:07'),('2038-01-19 03:14:08');
Warnings:
Warning 1264 Out of range value for column 'ts' at row 2
select * from t1;
ts
2038-01-19 03:14:07
0000-00-00 00:00:00
truncate table t1;
set time_zone='MET';
insert into t1 values ('2038-01-19 04:14:07'),('2038-01-19 04:14:08');
Warnings:
Warning 1264 Out of range value for column 'ts' at row 2
select * from t1;
ts
2038-01-19 04:14:07
0000-00-00 00:00:00
truncate table t1;
set time_zone='+01:30';
insert into t1 values ('2038-01-19 04:44:07'),('2038-01-19 04:44:08');
Warnings:
Warning 1264 Out of range value for column 'ts' at row 2
select * from t1;
ts
2038-01-19 04:44:07
0000-00-00 00:00:00
drop table t1;
SET time_zone='+00:00';
CREATE TABLE t1 (ts0 TIMESTAMP, ts1 TIMESTAMP(1));
INSERT INTO t1 VALUES ('2001-01-01 10:20:30', '2001-01-01 10:20:30.9');
# Corner case
UPDATE t1 SET ts1=FROM_UNIXTIME(2147483647.9);
UPDATE t1 SET ts0=COALESCE(ts1);
SELECT * FROM t1;
ts0 ts1
2038-01-19 03:14:07 2038-01-19 03:14:07.9
DROP TABLE t1;
SET time_zone=DEFAULT;
# Let us test CONVERT_TZ function
select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
convert_tz('2038-01-19 04:14:08', 'MET', 'UTC')
2038-01-19 04:14:08
select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
convert_tz('2103-01-01 04:00:00', 'MET', 'UTC')
2103-01-01 04:00:00
|
