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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
|
#
# Time related tests with canon differences for hosts with UNIX time
# representation of 32 or 64 bits.
# When we discontinue support for 32 bits time platforms, these can be
# moved (back) to where they came from.
#
# ------ 1. func_time.test BEGIN
#
# check for from_unixtime(2^31-1) and from_unixtime(2^31)
select from_unixtime(2147483647);
from_unixtime(2147483647)
2038-01-19 06:14:07
select from_unixtime(2147483648);
from_unixtime(2147483648)
2038-01-19 06:14:08
select from_unixtime(0);
from_unixtime(0)
1970-01-01 03:00:00
select from_unixtime(-1);
from_unixtime(-1)
NULL
select from_unixtime(-2147483648);
from_unixtime(-2147483648)
NULL
#
# Some more tests for bug #9191 "TIMESTAMP/from_unixtime() no
# longer accepts 2^31-1". Here we test that from_unixtime and
# unix_timestamp are consistent, when working with boundary dates.
#
select unix_timestamp(from_unixtime(2147483647));
unix_timestamp(from_unixtime(2147483647))
2147483647
select unix_timestamp(from_unixtime(2147483648));
unix_timestamp(from_unixtime(2147483648))
2147483648
# check for invalid dates
# bad year
select unix_timestamp('2039-01-20 01:00:00');
unix_timestamp('2039-01-20 01:00:00')
2179087200
select unix_timestamp('1968-01-20 01:00:00');
unix_timestamp('1968-01-20 01:00:00')
0
# bad month
select unix_timestamp('2038-02-10 01:00:00');
unix_timestamp('2038-02-10 01:00:00')
2149365600
select unix_timestamp('1969-11-20 01:00:00');
unix_timestamp('1969-11-20 01:00:00')
0
# bad day
select unix_timestamp('2038-01-20 01:00:00');
unix_timestamp('2038-01-20 01:00:00')
2147551200
select unix_timestamp('1969-12-30 01:00:00');
unix_timestamp('1969-12-30 01:00:00')
0
#
# Check negative shift (we subtract several days for boundary dates during
# conversion).
select unix_timestamp('2038-01-17 12:00:00');
unix_timestamp('2038-01-17 12:00:00')
2147331600
#
# Check positive shift. (it happens only on
# platfroms with unsigned time_t, such as QNX)
#
select unix_timestamp('1970-01-01 03:00:01');
unix_timestamp('1970-01-01 03:00:01')
1
# 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')
2147487247
# ------ 1. func_time.test END
# ------ 2. time_zone.test BEGIN
#
# Test for fix for Bug#2523 Check that boundary dates are processed
# correctly.
#
set time_zone="+01:00";
select unix_timestamp('1970-01-01 01:00:00'),
unix_timestamp('1970-01-01 01:00:01'),
unix_timestamp('2038-01-19 04:14:07'),
unix_timestamp('2038-01-19 04:14:08');
unix_timestamp('1970-01-01 01:00:00') unix_timestamp('1970-01-01 01:00:01') unix_timestamp('2038-01-19 04:14:07') unix_timestamp('2038-01-19 04:14:08')
0 1 2147483647 2147483648
set time_zone=default;
# ------ 2. time_zone.test END
# ------ 3. time_zone2.test BEGIN
select convert_tz('2038-01-19 04:14:08', '+01:00', 'UTC');
convert_tz('2038-01-19 04:14:08', '+01:00', 'UTC')
2038-01-19 03: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 03:00:00
Will work on a 64 bits time system, no-op on 32 bits time
select convert_tz('3001-01-18 23:59:59', 'UTC', '+01:00');
convert_tz('3001-01-18 23:59:59', 'UTC', '+01:00')
3001-01-19 00:59:59
Will not work, beyond range of UNIX timestamp support
select convert_tz('3001-01-19 00:00:00', 'UTC', '+01:00');
convert_tz('3001-01-19 00:00:00', 'UTC', '+01:00')
3001-01-19 00:00:00
# ------ 3. time_zone2.test END
# ------ 4. type_temporal_fractional.test BEGIN
#
# Testing rounding with FROM_UNIXTIME
#
SET time_zone='+00:00';
CREATE TABLE t1 (a DECIMAL(20,7));
INSERT INTO t1 VALUES
(32536771199.999999),
(32536771199.9999990),
(32536771199.9999991),
(32536771199.9999992),
(32536771199.9999993),
(32536771199.9999994),
(32536771199.9999995),
(32536771199.9999996),
(32536771199.9999997),
(32536771199.9999998),
(32536771199.9999999);
SELECT a, FROM_UNIXTIME(a) FROM t1;
a FROM_UNIXTIME(a)
32536771199.9999990 3001-01-18 23:59:59.999999
32536771199.9999990 3001-01-18 23:59:59.999999
32536771199.9999991 3001-01-18 23:59:59.999999
32536771199.9999992 3001-01-18 23:59:59.999999
32536771199.9999993 3001-01-18 23:59:59.999999
32536771199.9999994 3001-01-18 23:59:59.999999
32536771199.9999995 NULL
32536771199.9999996 NULL
32536771199.9999997 NULL
32536771199.9999998 NULL
32536771199.9999999 NULL
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# Bug #28671811 FROM_UNIXTIME ACCEPTS AN OUT OF RANGE PARAMETER
# THROUGH ROUNDING
#
SET time_zone='+00:00';
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3,
FROM_UNIXTIME(32536771199) AS c4,
FROM_UNIXTIME(32536771199.9999999) AS c5;
c1 c2 c3 c4 c5
2038-01-19 03:14:07 2038-01-19 03:14:08 2038-01-19 03:14:08.000000 3001-01-18 23:59:59 NULL
SET time_zone=default;
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3,
FROM_UNIXTIME(32536771199) AS c4,
FROM_UNIXTIME(32536771199.9999999) AS c5;
c1 c2 c3 c4 c5
2038-01-19 06:14:07 2038-01-19 06:14:08 2038-01-19 06:14:08.000000 3001-01-19 02:59:59 NULL
SET sql_mode=time_truncate_fractional;
SET time_zone='+00:00';
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3,
FROM_UNIXTIME(32536771199) AS c4,
FROM_UNIXTIME(32536771199.9999999) AS c5;
c1 c2 c3 c4 c5
2038-01-19 03:14:07 2038-01-19 03:14:08 2038-01-19 03:14:07.999999 3001-01-18 23:59:59 3001-01-18 23:59:59.999999
SET time_zone=default;
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3,
FROM_UNIXTIME(32536771199) AS c4,
FROM_UNIXTIME(32536771199.9999999) AS c5;
c1 c2 c3 c4 c5
2038-01-19 06:14:07 2038-01-19 06:14:08 2038-01-19 06:14:07.999999 3001-01-19 02:59:59 3001-01-19 02:59:59.999999
SET sql_mode=default;
# ------ 4. type_temporal_fractional.test END
# ------ 5 and 6. type_timestamp{_explicit}.test BEGIN
SET SESSION explicit_defaults_for_timestamp=OFF;
Warnings:
Warning 1287 'explicit_defaults_for_timestamp' is deprecated and will be removed in a future release.
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET TIMESTAMP=1000000019;
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00');
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
Warnings:
Warning 1264 Out of range value for column 'f3' at row 1
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME(99999999990));
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
Warnings:
Warning 1264 Out of range value for column 'f3' at row 1
SELECT f1,f2-f3 FROM t1;
f1 f2-f3
1 20010909044659
2 0
3 0
4 20010909044659
5 0
6 0
DROP TABLE t1;
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00');
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
SET SESSION explicit_defaults_for_timestamp=DEFAULT;
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
ERROR 23000: Column 'f3' cannot be null
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
ERROR 23000: Column 'f3' cannot be null
INSERT INTO t1 (f2,f3) VALUES (NOW(), COALESCE(FROM_UNIXTIME('9999999999'), "0000-00-00 00:00:00"));
Warnings:
Warning 1264 Out of range value for column 'f3' at row 1
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
ERROR 23000: Column 'f3' cannot be null
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME(99999999990));
ERROR 23000: Column 'f3' cannot be null
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
Warnings:
Warning 1264 Out of range value for column 'f3' at row 1
SELECT f1,f2-f3 FROM t1;
f1 f2-f3
1 20010909044659
2 20010909044659
DROP TABLE t1;
SET SESSION sql_mode=DEFAULT;
SET TIMESTAMP=0;
# ------ 5 and 6. type_timestamp{_explicit}.test END
# -- New tests added for 64 bits UNIXTIME
# Maximum value of 64 bits time
SET time_zone = UTC;
SELECT FROM_UNIXTIME(32536771199);
FROM_UNIXTIME(32536771199)
3001-01-18 23:59:59
SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
UNIX_TIMESTAMP('3001-01-18 23:59:59')
32536771199
Overflow
SELECT FROM_UNIXTIME(32536771200);
FROM_UNIXTIME(32536771200)
NULL
SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
UNIX_TIMESTAMP('3001-01-19 00:00:00')
0
"Explicit timezone indication: different code path than UTC"
SET time_zone = "+00:00";
Overflow
SELECT FROM_UNIXTIME(32536771200);
FROM_UNIXTIME(32536771200)
NULL
SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
UNIX_TIMESTAMP('3001-01-19 00:00:00')
0
SET time_zone = "+01:00";
# East of UTC
SELECT FROM_UNIXTIME(32536771199);
FROM_UNIXTIME(32536771199)
3001-01-19 00:59:59
SELECT UNIX_TIMESTAMP('3001-01-19 00:59:59');
UNIX_TIMESTAMP('3001-01-19 00:59:59')
32536771199
Overflow
SELECT FROM_UNIXTIME(32536771200);
FROM_UNIXTIME(32536771200)
NULL
SELECT UNIX_TIMESTAMP('3001-01-19 01:00:00');
UNIX_TIMESTAMP('3001-01-19 01:00:00')
0
SET time_zone = "Japan";
SELECT FROM_UNIXTIME(32536771199);
FROM_UNIXTIME(32536771199)
3001-01-19 08:59:59
SELECT UNIX_TIMESTAMP("3001-01-19 08:59:59");
UNIX_TIMESTAMP("3001-01-19 08:59:59")
32536771199
# Overflow:
SELECT UNIX_TIMESTAMP("3001-01-19 09:00:00");
UNIX_TIMESTAMP("3001-01-19 09:00:00")
0
SET time_zone = "-01:00";
# West of UTC
SELECT FROM_UNIXTIME(32536771199);
FROM_UNIXTIME(32536771199)
3001-01-18 22:59:59
SELECT UNIX_TIMESTAMP('3001-01-18 22:59:59');
UNIX_TIMESTAMP('3001-01-18 22:59:59')
32536771199
# Overflow:
SELECT FROM_UNIXTIME(32536771200);
FROM_UNIXTIME(32536771200)
NULL
SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
UNIX_TIMESTAMP('3001-01-18 23:59:59')
0
SET TIME_ZONE = "US/Pacific";
SELECT FROM_UNIXTIME(32536771199);
FROM_UNIXTIME(32536771199)
3001-01-18 15:59:59
SELECT UNIX_TIMESTAMP('3001-01-18 15:59:59');
UNIX_TIMESTAMP('3001-01-18 15:59:59')
32536771199
# Overflow:
SELECT UNIX_TIMESTAMP('3001-01-18 16:00:00');
UNIX_TIMESTAMP('3001-01-18 16:00:00')
0
Extreme values
SELECT FROM_UNIXTIME(9223372036854775807);
FROM_UNIXTIME(9223372036854775807)
NULL
SELECT FROM_UNIXTIME(-9223372036854775808);
FROM_UNIXTIME(-9223372036854775808)
NULL
SELECT FROM_UNIXTIME(9223372036854775808);
FROM_UNIXTIME(9223372036854775808)
NULL
SELECT FROM_UNIXTIME(99999999999999999999999999999999999999999999999999999999999999999);
FROM_UNIXTIME(99999999999999999999999999999999999999999999999999999999999999999)
NULL
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: '99999999999999999999999999999999999999999999999999999999999999999'
SET time_zone = DEFAULT;
|
