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
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
|
SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a time(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
create table t1 (a time(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
insert t1 values ('2010-12-11 15:47:11.1234');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
insert t1 values (20101211010203.45678);
Warnings:
Note 1265 Data truncated for column 'a' at row 1
insert t1 values (20101211030405.789e0);
Warnings:
Note 1265 Data truncated for column 'a' at row 1
insert t1 values (99991231235959e1);
Warnings:
Warning 1264 Out of range value for column 'a' at row 1
select * from t1;
a
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
838:59:59.999
select truncate(a, 6) from t1;
truncate(a, 6)
2003.123000
10203.456000
30405.789062
154711.123000
8385959.999000
select a DIV 1 from t1;
a DIV 1
2003
10203
30405
154711
8385959
select group_concat(distinct a) from t1;
group_concat(distinct a)
00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999
alter table t1 engine=innodb;
select * from t1 order by a;
a
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
838:59:59.999
select * from t1 order by a+0;
a
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
838:59:59.999
drop table t1;
create table t1 (a time(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
select * from t1;
a
01:02:03.4567
select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
extract(microsecond from a + interval 100 microsecond)
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
a
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` time(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show columns from t1;
Field Type Null Key Default Extra
a time(4) YES NULL
select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1';
table_name t1
column_name a
column_default NULL
is_nullable YES
data_type time
character_maximum_length NULL
character_octet_length NULL
numeric_precision NULL
numeric_scale NULL
datetime_precision 4
character_set_name NULL
collation_name NULL
column_type time(4)
column_key
extra
select a, a+interval 9876543 microsecond from t1;
a a+interval 9876543 microsecond
01:02:03.4567 01:02:13.333243
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
a
01:02:13.3332
select a, a + interval 2 year from t1;
a a + interval 2 year
01:02:13.3332 NULL
Warnings:
Warning 1441 Datetime function: time field overflow
insert t1 select a + interval 2 year from t1;
Warnings:
Warning 1441 Datetime function: time field overflow
select * from t1;
a
01:02:13.3332
NULL
delete from t1 where a < 20110101;
select * from t1;
a
NULL
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` time(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` time(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2, t3;
insert t1 values ('2010-12-13 14:15:16.222222');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
select a, a+0, a-1, a*1, a/2 from t1;
a a+0 a-1 a*1 a/2
NULL NULL NULL NULL NULL
14:15:16.2222 141516.2222 141515.2222 141516.2222 70758.11110000
select max(a), min(a), sum(a), avg(a) from t1;
max(a) min(a) sum(a) avg(a)
14:15:16.2222 14:15:16.2222 141516.2222 141516.22220000
create table t2 select a, a+0, a-1, a*1, a/2 from t1;
create table t3 select max(a), min(a), sum(a), avg(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` time(4) DEFAULT NULL,
`a+0` decimal(16,4) DEFAULT NULL,
`a-1` decimal(16,4) DEFAULT NULL,
`a*1` decimal(16,4) DEFAULT NULL,
`a/2` decimal(19,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`max(a)` time(4) DEFAULT NULL,
`min(a)` time(4) DEFAULT NULL,
`sum(a)` decimal(37,4) DEFAULT NULL,
`avg(a)` decimal(19,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 (f0_time time(0), f1_time time(1), f2_time time(2), f3_time time(3), f4_time time(4), f5_time time(5), f6_time time(6));
insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
Warnings:
Level Note
Code 1265
Message Data truncated for column 'f0_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f1_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f2_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f3_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f4_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f5_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'f6_time' at row 1
select * from t1;
f0_time 11:14:17
f1_time 11:14:17.7
f2_time 11:14:17.76
f3_time 11:14:17.765
f4_time 11:14:17.7654
f5_time 11:14:17.76543
f6_time 11:14:17.765432
select cast(f0_time as time(4)) time4_f0_time, cast(f1_time as datetime(3)) datetime3_f1_time, cast(f2_time as date) date_f2_time, cast(f4_time as double) double_f3_time, cast(f4_time as decimal(40,5)) decimal5_f4_time, cast(f5_time as signed) bigint_f5_time, cast(f6_time as char(255)) varchar_f6_time from t1;
time4_f0_time 11:14:17.0000
datetime3_f1_time 2001-02-03 11:14:17.700
date_f2_time 2001-02-03
double_f3_time 111417.7654
decimal5_f4_time 111417.76540
bigint_f5_time 111417
varchar_f6_time 11:14:17.765432
create table t2 (time4_f0_time time(4), datetime3_f1_time datetime(3), date_f2_time date, double_f3_time double, decimal5_f4_time decimal(40,5), bigint_f5_time bigint, varchar_f6_time varchar(255));
insert t2 select * from t1;
Warnings:
Level Note
Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t2;
time4_f0_time 11:14:17.0000
datetime3_f1_time 2001-02-03 11:14:17.700
date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
varchar_f6_time 11:14:17.765432
alter table t1 change f0_time time4_f0_time time(4), change f1_time datetime3_f1_time datetime(3), change f2_time date_f2_time date, change f3_time double_f3_time double, change f4_time decimal5_f4_time decimal(40,5), change f5_time bigint_f5_time bigint, change f6_time varchar_f6_time varchar(255);
Warnings:
Level Note
Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17.0000
datetime3_f1_time 2001-02-03 11:14:17.700
date_f2_time 2001-02-03
double_f3_time 111417.765
decimal5_f4_time 111417.76540
bigint_f5_time 111417
varchar_f6_time 11:14:17.765432
alter table t1 modify time4_f0_time time(0), modify datetime3_f1_time time(1), modify date_f2_time time(2), modify double_f3_time time(3), modify decimal5_f4_time time(4), modify bigint_f5_time time(5), modify varchar_f6_time time(6);
Warnings:
Level Note
Code 1265
Message Data truncated for column 'datetime3_f1_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
date_f2_time 00:00:00.00
double_f3_time 11:14:17.765
decimal5_f4_time 11:14:17.7654
bigint_f5_time 11:14:17.00000
varchar_f6_time 11:14:17.765432
delete from t1;
insert t1 select * from t2;
Warnings:
Level Note
Code 1265
Message Data truncated for column 'datetime3_f1_time' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_time' at row 1
select * from t1;
time4_f0_time 11:14:17
datetime3_f1_time 11:14:17.7
date_f2_time 00:00:00.00
double_f3_time 11:14:17.764
decimal5_f4_time 11:14:17.7654
bigint_f5_time 11:14:17.00000
varchar_f6_time 11:14:17.765432
drop table t1, t2;
create table t1 (a time(6), b time(6));
create procedure foo(x time, y time(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
Warnings:
Note 1265 Data truncated for column 'x' at row 1
Note 1265 Data truncated for column 'y' at row 1
select * from t1;
a b
04:05:06.000000 04:05:06.789100
create procedure bar(a int, c time(5))
begin
declare b time(4);
set b = c + interval a microsecond;
insert t1 values (b, c + interval a microsecond);
end|
call bar(1111111, '2011-01-02 3:4:5.123456');
Warnings:
Note 1265 Data truncated for column 'c' at row 1
select * from t1;
a b
04:05:06.000000 04:05:06.789100
03:04:06.234500 03:04:06.234561
drop procedure foo;
drop procedure bar;
create function xyz(s char(20)) returns time(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
xyz('1:1:1.010101')
11:11:11.1111
Warnings:
Note 1265 Data truncated for column 'xyz('1:1:1.010101')' at row 1
drop function xyz;
create view v1 as select * from t1 group by a,b;
select * from v1;
a b
03:04:06.234500 03:04:06.234561
04:05:06.000000 04:05:06.789100
show columns from v1;
Field Type Null Key Default Extra
a time(6) YES NULL
b time(6) YES NULL
create table t2 select * from v1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` time(6) DEFAULT NULL,
`b` time(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
a b
03:04:06.234500 03:04:06.234561
04:05:06.000000 04:05:06.789100
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
create table t1 (a time(4) not null, key(a));
insert into t1 values ('1:2:3.001'),('1:2:3'), ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
select * from t1 order by a;
a
-00:00:01.1000
-00:00:01.1000
-00:00:01.0900
-00:00:01.0000
-00:00:01.0000
-00:00:01.0000
-00:00:00.9000
-00:00:00.8000
-00:00:00.7000
-00:00:00.6000
01:02:03.0000
01:02:03.0010
select * from t1 order by a desc;
a
01:02:03.0010
01:02:03.0000
-00:00:00.6000
-00:00:00.7000
-00:00:00.8000
-00:00:00.9000
-00:00:01.0000
-00:00:01.0000
-00:00:01.0000
-00:00:01.0900
-00:00:01.1000
-00:00:01.1000
select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0;
min(a - interval 1 hour) max(a - interval 1 hour)
-01:00:01.1000 -01:00:00.6000
drop table t1;
select cast(1e-6 as time(6));
cast(1e-6 as time(6))
00:00:00.000001
|
