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
|
SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a timestamp(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
create table t1 (a timestamp(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
insert t1 values ('2010-12-11 15:47:11.1234');
insert t1 values (20101211010203.45678);
insert t1 values (20101211030405.789e0);
insert t1 values (99991231235959e1);
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select truncate(a, 6) from t1;
truncate(a, 6)
0.000000
20101211002003.120000
20101211010203.457031
20101211030405.790000
20101211154711.120000
select a DIV 1 from t1;
a DIV 1
0
20101211002003
20101211010203
20101211030405
20101211154711
select group_concat(distinct a) from t1;
group_concat(distinct a)
0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123
alter table t1 engine=innodb;
select * from t1 order by a;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select * from t1 order by a+0;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
drop table t1;
create table t1 (a timestamp(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
select * from t1;
a
2010-12-11 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)
456800
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
a
2010-12-11 01:02:03.4567
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show columns from t1;
Field Type Null Key Default Extra
a timestamp(4) NO CURRENT_TIMESTAMP(4) on update CURRENT_TIMESTAMP
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 CURRENT_TIMESTAMP(4)
is_nullable NO
data_type timestamp
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 timestamp(4)
column_key
extra on update CURRENT_TIMESTAMP
select a, a+interval 9876543 microsecond from t1;
a a+interval 9876543 microsecond
2010-12-11 01:02:03.4567 2010-12-11 01:02:13.333243
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
a
2010-12-11 01:02:13.3332
select a, a + interval 2 year from t1;
a a + interval 2 year
2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332
insert t1 select a + interval 2 year from t1;
select * from t1;
a
2010-12-11 01:02:13.3332
2012-12-11 01:02:13.3332
delete from t1 where a < 20110101;
select * from t1;
a
2012-12-11 01:02:13.3332
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2, t3;
insert t1 values ('2010-12-13 14:15:16.222222');
select a, a+0, a-1, a*1, a/2 from t1;
a a+0 a-1 a*1 a/2
2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000
2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000
select max(a), min(a), sum(a), avg(a) from t1;
max(a) min(a) sum(a) avg(a)
2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000
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` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4),
`a+0` decimal(25,4) NOT NULL,
`a-1` decimal(25,4) NOT NULL,
`a*1` decimal(25,4) NOT NULL,
`a/2` decimal(28,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`max(a)` timestamp(4) NULL DEFAULT NULL,
`min(a)` timestamp(4) NULL DEFAULT NULL,
`sum(a)` decimal(46,4) DEFAULT NULL,
`avg(a)` decimal(28,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 (f0_timestamp timestamp(0), f1_timestamp timestamp(1), f2_timestamp timestamp(2), f3_timestamp timestamp(3), f4_timestamp timestamp(4), f5_timestamp timestamp(5), f6_timestamp timestamp(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');
select * from t1;
f0_timestamp 2010-11-12 11:14:17
f1_timestamp 2010-11-12 11:14:17.7
f2_timestamp 2010-11-12 11:14:17.76
f3_timestamp 2010-11-12 11:14:17.765
f4_timestamp 2010-11-12 11:14:17.7654
f5_timestamp 2010-11-12 11:14:17.76543
f6_timestamp 2010-11-12 11:14:17.765432
select cast(f0_timestamp as time(4)) time4_f0_timestamp, cast(f1_timestamp as datetime(3)) datetime3_f1_timestamp, cast(f2_timestamp as date) date_f2_timestamp, cast(f4_timestamp as double) double_f3_timestamp, cast(f4_timestamp as decimal(40,5)) decimal5_f4_timestamp, cast(f5_timestamp as signed) bigint_f5_timestamp, cast(f6_timestamp as char(255)) varchar_f6_timestamp from t1;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
create table t2 (time4_f0_timestamp time(4), datetime3_f1_timestamp datetime(3), date_f2_timestamp date, double_f3_timestamp double, decimal5_f4_timestamp decimal(40,5), bigint_f5_timestamp bigint, varchar_f6_timestamp varchar(255));
insert t2 select * from t1;
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_timestamp' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_timestamp' at row 1
select * from t2;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 change f0_timestamp time4_f0_timestamp time(4), change f1_timestamp datetime3_f1_timestamp datetime(3), change f2_timestamp date_f2_timestamp date, change f3_timestamp double_f3_timestamp double, change f4_timestamp decimal5_f4_timestamp decimal(40,5), change f5_timestamp bigint_f5_timestamp bigint, change f6_timestamp varchar_f6_timestamp varchar(255);
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_timestamp' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_timestamp' at row 1
select * from t1;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_timestamp timestamp(0), modify datetime3_f1_timestamp timestamp(1), modify date_f2_timestamp timestamp(2), modify double_f3_timestamp timestamp(3), modify decimal5_f4_timestamp timestamp(4), modify bigint_f5_timestamp timestamp(5), modify varchar_f6_timestamp timestamp(6);
select * from t1;
time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.766
decimal5_f4_timestamp 2010-11-12 11:14:17.7654
bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.765
decimal5_f4_timestamp 2010-11-12 11:14:17.7654
bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
drop table t1, t2;
create table t1 (a timestamp(6), b timestamp(6));
create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
create procedure bar(a int, c timestamp(5))
begin
declare b timestamp(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');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop procedure foo;
drop procedure bar;
create function xyz(s char(20)) returns timestamp(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
xyz('1:1:1.010101')
2010-10-10 11:11:11.1111
drop function xyz;
create view v1 as select * from t1 group by a,b;
select * from v1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
show columns from v1;
Field Type Null Key Default Extra
a timestamp(6) NO 0000-00-00 00:00:00.000000
b timestamp(6) NO 0000-00-00 00:00:00.000000
create table t2 select * from v1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
`b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
set time_zone='+03:00';
set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456;
create table t1 (a timestamp(5));
insert t1 values ();
select * from t1;
a
2011-01-01 01:01:01.12345
drop table t1;
create table t1 (a timestamp(5) default current_timestamp);
drop table t1;
create table t1 (a timestamp(5) default current_timestamp());
drop table t1;
create table t1 (a timestamp(5) default current_timestamp(2));
ERROR 42000: Invalid default value for 'a'
create table t1 (a timestamp(5) default current_timestamp(5));
drop table t1;
create table t1 (a timestamp(5) default current_timestamp(6));
drop table t1;
create table t1 (a timestamp(5) on update current_timestamp);
drop table t1;
create table t1 (a timestamp(5) on update current_timestamp());
drop table t1;
create table t1 (a timestamp(5) on update current_timestamp(3));
ERROR HY000: Invalid ON UPDATE clause for 'a' column
create table t1 (a timestamp(5) on update current_timestamp(5));
drop table t1;
create table t1 (a timestamp(5) on update current_timestamp(6));
drop table t1;
|
