1
DROP TABLE IF EXISTS t1;
3
# Test with a saved table from 5.5
5
SET time_zone='+03:00';
9
`f_time` time DEFAULT NULL,
10
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11
`f_datetime` datetime DEFAULT NULL
12
) ENGINE=MyISAM DEFAULT CHARSET=latin1
13
INSERT INTO t1 (f_time, f_datetime, f_timestamp)
14
VALUES (9999999, 999999999999999, 999999999999999);
16
Warning 1264 Out of range value for column 'f_time' at row 1
17
Warning 1264 Out of range value for column 'f_datetime' at row 1
18
Warning 1264 Out of range value for column 'f_timestamp' at row 1
19
INSERT INTO t1 (f_time, f_datetime, f_timestamp)
20
VALUES (-9999999, -999999999999999, -999999999999999);
22
Warning 1264 Out of range value for column 'f_time' at row 1
23
Warning 1265 Data truncated for column 'f_datetime' at row 1
24
Warning 1264 Out of range value for column 'f_timestamp' at row 1
25
INSERT INTO t1 (f_time, f_datetime, f_timestamp)
26
VALUES ('00:00:00', '0000-00-00 00:00:00','0000-00-00 00:00:00');
28
f_time f_timestamp f_datetime
29
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
30
00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01
31
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
32
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
33
00:00:00 2011-11-21 17:11:01 NULL
34
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
35
-838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
36
00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
37
SELECT * FROM t1 ORDER BY f_time;
38
f_time f_timestamp f_datetime
39
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
40
-838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
41
00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01
42
00:00:00 2011-11-21 17:11:01 NULL
43
00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
44
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
45
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
46
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
47
SELECT * FROM t1 WHERE f_time > '00:00:00';
48
f_time f_timestamp f_datetime
49
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
50
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
51
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
52
SELECT * FROM t1 WHERE f_time > '00:00:00' ORDER BY f_time;
53
f_time f_timestamp f_datetime
54
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
55
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
56
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
57
SELECT * FROM t1 WHERE f_time = '01:01:10';
58
f_time f_timestamp f_datetime
59
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
60
SELECT * FROM t1 WHERE f_time IS NULL;
61
f_time f_timestamp f_datetime
62
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
63
SELECT COUNT(*), MIN(f_time) FROM t1 GROUP BY f_time;
71
SELECT * FROM t1 ORDER BY f_timestamp;
72
f_time f_timestamp f_datetime
73
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
74
-838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
75
00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
76
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
77
00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01
78
00:00:00 2011-11-21 17:11:01 NULL
79
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
80
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
81
SELECT * FROM t1 WHERE f_timestamp > '2011-11-21 17:11:01';
82
f_time f_timestamp f_datetime
83
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
84
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
85
SELECT * FROM t1 WHERE f_timestamp > '2011-11-21 17:11:01' ORDER BY f_timestamp;
86
f_time f_timestamp f_datetime
87
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
88
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
89
SELECT * FROM t1 WHERE f_timestamp = '2015-11-21 17:11:01';
90
f_time f_timestamp f_datetime
91
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
92
SELECT * FROM t1 WHERE f_timestamp IS NULL;
93
f_time f_timestamp f_datetime
94
SELECT COUNT(*), MIN(f_timestamp) FROM t1 GROUP BY f_timestamp;
95
COUNT(*) MIN(f_timestamp)
100
1 2015-11-21 17:11:01
101
1 2031-11-21 17:11:01
102
SELECT UNIX_TIMESTAMP(f_timestamp) FROM t1 ORDER BY f_timestamp;
103
UNIX_TIMESTAMP(f_timestamp)
112
SELECT * FROM t1 ORDER BY f_datetime;
113
f_time f_timestamp f_datetime
114
00:00:00 2011-11-21 17:11:01 NULL
115
838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
116
-838:59:59 0000-00-00 00:00:00 0000-00-00 00:00:00
117
00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
118
00:00:00 2001-01-21 18:11:01 2000-01-01 01:01:01
119
NULL 2015-11-21 17:11:01 2000-01-01 01:01:01
120
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
121
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
122
SELECT * FROM t1 WHERE f_datetime > '2000-01-01 01:01:01';
123
f_time f_timestamp f_datetime
124
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
125
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
126
SELECT * FROM t1 WHERE f_datetime > '2000-01-01 01:01:01' ORDER BY f_datetime;
127
f_time f_timestamp f_datetime
128
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
129
01:01:10 1980-11-21 18:06:01 2020-01-01 01:01:01
130
SELECT * FROM t1 WHERE f_datetime = '2010-10-10 10:10:10';
131
f_time f_timestamp f_datetime
132
10:10:10 2031-11-21 17:11:01 2010-10-10 10:10:10
133
SELECT * FROM t1 WHERE f_datetime IS NULL;
134
f_time f_timestamp f_datetime
135
00:00:00 2011-11-21 17:11:01 NULL
136
SELECT COUNT(*), MIN(f_datetime) FROM t1 GROUP BY f_datetime;
137
COUNT(*) MIN(f_datetime)
139
3 0000-00-00 00:00:00
140
2 2000-01-01 01:01:01
141
1 2010-10-10 10:10:10
142
1 2020-01-01 01:01:01
144
# Checking various val_xxx() methods for the old fields
146
SELECT CAST(f_time AS CHAR), CAST(f_time AS SIGNED), CAST(f_time AS DECIMAL(23,6)) FROM t1;
147
CAST(f_time AS CHAR) CAST(f_time AS SIGNED) CAST(f_time AS DECIMAL(23,6))
148
10:10:10 101010 101010.000000
150
01:01:10 10110 10110.000000
153
838:59:59 8385959 8385959.000000
154
-838:59:59 -8385959 -8385959.000000
156
SELECT CAST(f_datetime AS CHAR), CAST(f_datetime AS SIGNED), CAST(f_datetime AS DECIMAL(23,6)) FROM t1;
157
CAST(f_datetime AS CHAR) CAST(f_datetime AS SIGNED) CAST(f_datetime AS DECIMAL(23,6))
158
2010-10-10 10:10:10 20101010101010 20101010101010.000000
159
2000-01-01 01:01:01 20000101010101 20000101010101.000000
160
2020-01-01 01:01:01 20200101010101 20200101010101.000000
161
2000-01-01 01:01:01 20000101010101 20000101010101.000000
163
0000-00-00 00:00:00 0 0.000000
164
0000-00-00 00:00:00 0 0.000000
165
0000-00-00 00:00:00 0 0.000000
166
SELECT CAST(f_timestamp AS CHAR), CAST(f_timestamp AS SIGNED), CAST(f_timestamp AS DECIMAL(23,6)) FROM t1;
167
CAST(f_timestamp AS CHAR) CAST(f_timestamp AS SIGNED) CAST(f_timestamp AS DECIMAL(23,6))
168
2031-11-21 17:11:01 20311121171101 20311121171101.000000
169
2001-01-21 18:11:01 20010121181101 20010121181101.000000
170
1980-11-21 18:06:01 19801121180601 19801121180601.000000
171
2015-11-21 17:11:01 20151121171101 20151121171101.000000
172
2011-11-21 17:11:01 20111121171101 20111121171101.000000
173
0000-00-00 00:00:00 0 0.000000
174
0000-00-00 00:00:00 0 0.000000
175
0000-00-00 00:00:00 0 0.000000
179
INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5');
180
SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1;
181
f_time HEX(WEIGHT_STRING(f_time))
191
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
192
f_timestamp HEX(WEIGHT_STRING(f_timestamp))
193
2031-11-21 17:11:01 7468F975
194
2001-01-21 18:11:01 3A6AFC05
195
1980-11-21 18:06:01 147BF1D9
196
2015-11-21 17:11:01 56507B75
197
2011-11-21 17:11:01 4ECA5BF5
198
0000-00-00 00:00:00 00000000
199
0000-00-00 00:00:00 00000000
200
0000-00-00 00:00:00 00000000
201
2011-11-21 22:22:23 4ECAA4EF
202
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
203
f_datetime HEX(WEIGHT_STRING(f_datetime))
204
2010-10-10 10:10:10 0000124821911312
205
2000-01-01 01:01:01 00001230A2EA8AB5
206
2020-01-01 01:01:01 0000125F33D85AB5
207
2000-01-01 01:01:01 00001230A2EA8AB5
209
0000-00-00 00:00:00 0000000000000000
210
0000-00-00 00:00:00 0000000000000000
211
0000-00-00 00:00:00 0000000000000000
212
2011-11-21 22:22:23 0000124A7C3C8A4F
214
# This ALTER does not change old fields to new fields
215
# Still expect old WEIGHT_STRING for the TIME and DATETIME fields
217
ALTER TABLE t1 FORCE;
218
INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5');
219
SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1;
220
f_time HEX(WEIGHT_STRING(f_time))
231
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
232
f_timestamp HEX(WEIGHT_STRING(f_timestamp))
233
2031-11-21 17:11:01 7468F975
234
2001-01-21 18:11:01 3A6AFC05
235
1980-11-21 18:06:01 147BF1D9
236
2015-11-21 17:11:01 56507B75
237
2011-11-21 17:11:01 4ECA5BF5
238
0000-00-00 00:00:00 00000000
239
0000-00-00 00:00:00 00000000
240
0000-00-00 00:00:00 00000000
241
2011-11-21 22:22:23 4ECAA4EF
242
2011-11-21 22:22:23 4ECAA4EF
243
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
244
f_datetime HEX(WEIGHT_STRING(f_datetime))
245
2010-10-10 10:10:10 0000124821911312
246
2000-01-01 01:01:01 00001230A2EA8AB5
247
2020-01-01 01:01:01 0000125F33D85AB5
248
2000-01-01 01:01:01 00001230A2EA8AB5
250
0000-00-00 00:00:00 0000000000000000
251
0000-00-00 00:00:00 0000000000000000
252
0000-00-00 00:00:00 0000000000000000
253
2011-11-21 22:22:23 0000124A7C3C8A4F
254
2011-11-21 22:22:23 0000124A7C3C8A4F
256
# This ALTER should change old fields to new fields
257
# Expecting new WEIGHT_STRING the rounding.
259
ALTER TABLE t1 MODIFY f_time TIME, MODIFY f_timestamp TIMESTAMP, MODIFY f_datetime DATETIME;
260
INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5');
261
SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1;
262
f_time HEX(WEIGHT_STRING(f_time))
274
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
275
f_timestamp HEX(WEIGHT_STRING(f_timestamp))
276
2031-11-21 17:11:01 7468F975
277
2001-01-21 18:11:01 3A6AFC05
278
1980-11-21 18:06:01 147BF1D9
279
2015-11-21 17:11:01 56507B75
280
2011-11-21 17:11:01 4ECA5BF5
281
0000-00-00 00:00:00 00000000
282
0000-00-00 00:00:00 00000000
283
0000-00-00 00:00:00 00000000
284
2011-11-21 22:22:23 4ECAA4EF
285
2011-11-21 22:22:23 4ECAA4EF
286
2011-11-21 22:22:23 4ECAA4EF
287
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
288
f_datetime HEX(WEIGHT_STRING(f_datetime))
289
2010-10-10 10:10:10 998714A28A
290
2000-01-01 01:01:01 9964421041
291
2020-01-01 01:01:01 99A5421041
292
2000-01-01 01:01:01 9964421041
294
0000-00-00 00:00:00 8000000000
295
0000-00-00 00:00:00 8000000000
296
0000-00-00 00:00:00 8000000000
297
2011-11-21 22:22:23 998AAB6597
298
2011-11-21 22:22:23 998AAB6597
299
2011-11-21 22:22:23 998AAB6597
301
SET time_zone=DEFAULT;