1
drop table if exists t1,t2;
2
CREATE TABLE t1 (a int, t timestamp);
3
CREATE TABLE t2 (a int, t datetime);
5
insert into t1 values(1,NULL);
6
insert into t1 values(2,"2002-03-03");
8
insert into t1 values(3,NULL);
10
insert into t1 (a) values(4);
11
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,NULL);
13
insert into t1 select * from t2;
15
insert into t1 (a) select a+1 from t2 where a=8;
29
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
30
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
31
SELECT stamp FROM t1 WHERE id="myKey";
34
UPDATE t1 SET value="my value" WHERE id="myKey";
35
SELECT stamp FROM t1 WHERE id="myKey";
38
UPDATE t1 SET id="myKey" WHERE value="my value";
39
SELECT stamp FROM t1 WHERE id="myKey";
43
create table t1 (a timestamp);
44
insert into t1 values (now());
45
select date_format(a,"%Y %y"),year(a),year(now()) from t1;
46
date_format(a,"%Y %y") year(a) year(now())
49
create table t1 (ix timestamp);
50
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
63
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
71
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
72
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
73
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
74
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
75
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
76
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
77
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
78
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
79
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
80
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
81
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
82
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
83
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
85
date date_time time_stamp
86
1998-12-31 1998-12-31 23:59:59 1998-12-31 23:59:59
87
1999-01-01 1999-01-01 00:00:00 1999-01-01 00:00:00
88
1999-09-09 1999-09-09 23:59:59 1999-09-09 23:59:59
89
2000-01-01 2000-01-01 00:00:00 2000-01-01 00:00:00
90
2000-02-28 2000-02-28 00:00:00 2000-02-28 00:00:00
91
2000-02-29 2000-02-29 00:00:00 2000-02-29 00:00:00
92
2000-03-01 2000-03-01 00:00:00 2000-03-01 00:00:00
93
2000-12-31 2000-12-31 23:59:59 2000-12-31 23:59:59
94
2001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00
95
2004-12-31 2004-12-31 23:59:59 2004-12-31 23:59:59
96
2005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00
97
2030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00
99
create table t1 (t2 timestamp, t4 timestamp, t6 timestamp,
100
t8 timestamp, t10 timestamp, t12 timestamp,
103
("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
104
"1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
105
"1997-12-31 23:47:59");
107
t2 t4 t6 t8 t10 t12 t14
108
1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59
110
t2 t4 t6 t8 t10 t12 t14
111
1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59
113
create table t1 (ix timestamp);
114
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
115
ERROR HY000: Received an invalid timestamp value '0'.
119
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
120
ERROR HY000: Received an invalid timestamp value '00000000000000'.
124
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
125
ERROR HY000: Received an invalid timestamp value '0000-00-00 00:00:00 some trailer'.
129
create table t1 (t1 timestamp default now(), t2 timestamp on update now());
130
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
131
create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now());
132
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
133
create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp);
134
SET TIMESTAMP=1000000000;
135
insert into t1 values ();
136
SET TIMESTAMP=1000000001;
137
update t1 set t2=now();
138
SET TIMESTAMP=1000000002;
139
insert into t1 (t1,t3) values (default, default);
142
2003-01-01 00:00:00 2001-09-09 01:46:41 NULL
143
2003-01-01 00:00:00 NULL NULL
144
show create table t1;
146
t1 CREATE TABLE `t1` (
147
`t1` TIMESTAMP NULL DEFAULT '2003-01-01 00:00:00',
148
`t2` DATETIME DEFAULT NULL,
149
`t3` TIMESTAMP NULL DEFAULT NULL
150
) ENGINE=DEFAULT COLLATE = utf8_general_ci
151
show columns from t1;
152
Field Type Null Default Default_is_NULL On_Update
153
t1 TIMESTAMP YES 2003-01-01 00:00:00 NO
157
create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp);
158
SET TIMESTAMP=1000000002;
159
insert into t1 values ();
160
SET TIMESTAMP=1000000003;
161
update t1 set t2=now();
162
SET TIMESTAMP=1000000003;
163
insert into t1 (t1,t3) values (default, default);
166
2001-09-09 01:46:42 2001-09-09 01:46:43 NULL
167
2001-09-09 01:46:43 NULL NULL
168
show create table t1;
170
t1 CREATE TABLE `t1` (
171
`t1` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
172
`t2` DATETIME DEFAULT NULL,
173
`t3` TIMESTAMP NULL DEFAULT NULL
174
) ENGINE=DEFAULT COLLATE = utf8_general_ci
175
show columns from t1;
176
Field Type Null Default Default_is_NULL On_Update
177
t1 TIMESTAMP YES CURRENT_TIMESTAMP YES
181
create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime);
182
SET TIMESTAMP=1000000004;
183
insert into t1 values ();
186
2003-01-01 00:00:00 NULL
187
SET TIMESTAMP=1000000005;
188
update t1 set t2=now();
189
SET TIMESTAMP=1000000005;
190
insert into t1 (t1) values (default);
193
2001-09-09 01:46:45 2001-09-09 01:46:45
194
2003-01-01 00:00:00 NULL
195
show create table t1;
197
t1 CREATE TABLE `t1` (
198
`t1` TIMESTAMP NULL DEFAULT '2003-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
199
`t2` DATETIME DEFAULT NULL
200
) ENGINE=DEFAULT COLLATE = utf8_general_ci
201
show columns from t1;
202
Field Type Null Default Default_is_NULL On_Update
203
t1 TIMESTAMP YES 2003-01-01 00:00:00 NO CURRENT_TIMESTAMP
206
create table t1 (t1 timestamp default now() on update now(), t2 datetime);
207
SET TIMESTAMP=1000000006;
208
insert into t1 values ();
211
2001-09-09 01:46:46 NULL
212
SET TIMESTAMP=1000000007;
213
update t1 set t2=now();
214
SET TIMESTAMP=1000000007;
215
insert into t1 (t1) values (default);
218
2001-09-09 01:46:47 2001-09-09 01:46:47
219
2001-09-09 01:46:47 NULL
220
show create table t1;
222
t1 CREATE TABLE `t1` (
223
`t1` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
224
`t2` DATETIME DEFAULT NULL
225
) ENGINE=DEFAULT COLLATE = utf8_general_ci
226
show columns from t1;
227
Field Type Null Default Default_is_NULL On_Update
228
t1 TIMESTAMP YES CURRENT_TIMESTAMP YES CURRENT_TIMESTAMP
231
create table t1 (t1 timestamp, t2 datetime, t3 timestamp);
232
SET TIMESTAMP=1000000007;
233
insert into t1 values ();
237
SET TIMESTAMP=1000000008;
238
update t1 set t2=now();
239
SET TIMESTAMP=1000000008;
240
insert into t1 (t1,t3) values (default, default);
243
NULL 2001-09-09 01:46:48 NULL
245
show create table t1;
247
t1 CREATE TABLE `t1` (
248
`t1` TIMESTAMP NULL DEFAULT NULL,
249
`t2` DATETIME DEFAULT NULL,
250
`t3` TIMESTAMP NULL DEFAULT NULL
251
) ENGINE=DEFAULT COLLATE = utf8_general_ci
252
show columns from t1;
253
Field Type Null Default Default_is_NULL On_Update
258
create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
259
SET TIMESTAMP=1000000009;
260
insert into t1 values ();
263
2001-09-09 01:46:49 NULL
264
SET TIMESTAMP=1000000010;
265
update t1 set t2=now();
266
SET TIMESTAMP=1000000011;
267
insert into t1 (t1) values (default);
270
2001-09-09 01:46:50 2001-09-09 01:46:50
271
2001-09-09 01:46:51 NULL
272
show create table t1;
274
t1 CREATE TABLE `t1` (
275
`t1` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
276
`t2` DATETIME DEFAULT NULL
277
) ENGINE=DEFAULT COLLATE = utf8_general_ci
278
show columns from t1;
279
Field Type Null Default Default_is_NULL On_Update
280
t1 TIMESTAMP YES CURRENT_TIMESTAMP YES CURRENT_TIMESTAMP
283
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
284
SET TIMESTAMP=1000000012;
285
update t1 set t1= '2004-04-02 00:00:00';
288
2004-04-02 00:00:00 2004-04-01 00:00:00
289
update t1 set t1.t1= '2004-04-03 00:00:00';
292
2004-04-03 00:00:00 2004-04-01 00:00:00
294
create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
295
insert into t1 values (1, '2004-04-01 00:00:00', 10);
296
SET TIMESTAMP=1000000013;
297
replace into t1 set pk = 1, bulk= 20;
300
1 2001-09-09 01:46:53 20
302
create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
303
insert into t1 values (1, '2004-04-01 00:00:00', 10);
304
SET TIMESTAMP=1000000014;
305
replace into t1 set pk = 1, bulk= 20;
308
1 2003-01-01 00:00:00 20
310
create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
311
insert into t1 values (1, '2004-04-01 00:00:00', 10);
312
SET TIMESTAMP=1000000015;
313
replace into t1 set pk = 1, bulk= 20;
316
1 2001-09-09 01:46:55 20
318
create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
319
insert into t1 values ('2004-04-01 00:00:00');
320
SET TIMESTAMP=1000000016;
321
alter table t1 add i int default 10;
324
2004-04-01 00:00:00 10
326
create table t1 (a timestamp null, b timestamp null);
327
show create table t1;
329
t1 CREATE TABLE `t1` (
330
`a` TIMESTAMP NULL DEFAULT NULL,
331
`b` TIMESTAMP NULL DEFAULT NULL
332
) ENGINE=DEFAULT COLLATE = utf8_general_ci
333
insert into t1 values (NULL, NULL);
334
SET TIMESTAMP=1000000017;
335
insert into t1 values ();
341
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
342
show create table t1;
344
t1 CREATE TABLE `t1` (
345
`a` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
346
`b` TIMESTAMP NULL DEFAULT NULL
347
) ENGINE=DEFAULT COLLATE = utf8_general_ci
348
insert into t1 values (NULL, NULL);
349
SET TIMESTAMP=1000000018;
350
insert into t1 values ();
354
2001-09-09 01:46:58 NULL
356
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
357
show create table t1;
359
t1 CREATE TABLE `t1` (
360
`a` TIMESTAMP NULL DEFAULT NULL,
361
`b` TIMESTAMP NULL DEFAULT '2003-01-01 00:00:00'
362
) ENGINE=DEFAULT COLLATE = utf8_general_ci
363
insert into t1 values (NULL, NULL);
364
insert into t1 values (DEFAULT, DEFAULT);
368
NULL 2003-01-01 00:00:00
370
create table t1 (a bigint, b bigint);
371
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
372
set timestamp=1000000019;
373
alter table t1 modify a timestamp, modify b timestamp;
377
2003-01-01 00:00:00 2003-01-02 00:00:00
379
create table t1 (a char(2), t timestamp);
380
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
381
('b', '2004-02-01 00:00:00');
382
select max(t) from t1 group by a;
387
create table t1 (a int auto_increment primary key, b int, c timestamp);
388
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
389
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
392
1 0 2001-01-01 01:01:01
393
2 0 2002-02-02 02:02:02
394
3 0 2003-03-03 03:03:03
395
update t1 set b = 2, c = c where a = 2;
398
1 0 2001-01-01 01:01:01
399
2 2 2002-02-02 02:02:02
400
3 0 2003-03-03 03:03:03
401
insert into t1 (a) values (4);
404
1 0 2001-01-01 01:01:01
405
2 2 2002-02-02 02:02:02
406
3 0 2003-03-03 03:03:03
408
update t1 set c = '2004-04-04 04:04:04' where a = 4;
411
1 0 2001-01-01 01:01:01
412
2 2 2002-02-02 02:02:02
413
3 0 2003-03-03 03:03:03
414
4 NULL 2004-04-04 04:04:04
415
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
418
1 0 2001-01-01 01:01:01
419
2 2 2002-02-02 02:02:02
420
3 3 2003-03-03 03:03:03
421
4 NULL 2004-04-04 04:04:04
423
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
424
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
427
1 0 2001-01-01 01:01:01
428
2 2 2002-02-02 02:02:02
429
3 3 2003-03-03 03:03:03
430
4 4 2004-04-04 04:04:04
432
6 NULL 2006-06-06 06:06:06
435
`id` int NOT NULL auto_increment,
436
`username` varchar(80) NOT NULL default '',
437
`posted_on` timestamp NOT NULL default now(),
441
Field Type Null Default Default_is_NULL On_Update
443
username VARCHAR NO NO
444
posted_on TIMESTAMP NO CURRENT_TIMESTAMP NO
445
select is_nullable from data_dictionary.columns where TABLE_NAME='t1' and COLUMN_NAME='posted_on';