1
drop table if exists t1,t2;
2
set time_zone="+03:00";
3
CREATE TABLE t1 (a int, t timestamp);
4
CREATE TABLE t2 (a int, t datetime);
6
insert into t1 values(1,NULL);
7
insert into t1 values(2,"2002-03-03");
9
insert into t1 values(3,NULL);
11
insert into t1 (a) values(4);
12
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
14
insert into t1 select * from t2;
16
insert into t1 (a) select a+1 from t2 where a=8;
30
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
31
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
32
SELECT stamp FROM t1 WHERE id="myKey";
35
UPDATE t1 SET value="my value" WHERE id="myKey";
36
SELECT stamp FROM t1 WHERE id="myKey";
39
UPDATE t1 SET id="myKey" WHERE value="my value";
40
SELECT stamp FROM t1 WHERE id="myKey";
44
create table t1 (a timestamp);
45
insert into t1 values (now());
46
select date_format(a,"%Y %y"),year(a),year(now()) from t1;
47
date_format(a,"%Y %y") year(a) year(now())
50
create table t1 (ix timestamp);
51
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
64
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
72
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
73
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
74
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
75
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
76
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
77
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
78
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
79
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
80
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
81
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
82
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
83
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
84
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
86
date date_time time_stamp
87
1998-12-31 1998-12-31 23:59:59 1998-12-31 23:59:59
88
1999-01-01 1999-01-01 00:00:00 1999-01-01 00:00:00
89
1999-09-09 1999-09-09 23:59:59 1999-09-09 23:59:59
90
2000-01-01 2000-01-01 00:00:00 2000-01-01 00:00:00
91
2000-02-28 2000-02-28 00:00:00 2000-02-28 00:00:00
92
2000-02-29 2000-02-29 00:00:00 2000-02-29 00:00:00
93
2000-03-01 2000-03-01 00:00:00 2000-03-01 00:00:00
94
2000-12-31 2000-12-31 23:59:59 2000-12-31 23:59:59
95
2001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00
96
2004-12-31 2004-12-31 23:59:59 2004-12-31 23:59:59
97
2005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00
98
2030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00
100
create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6),
101
t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
104
Warning 1287 'TIMESTAMP(2)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
105
Warning 1287 'TIMESTAMP(4)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
106
Warning 1287 'TIMESTAMP(6)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
107
Warning 1287 'TIMESTAMP(8)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
108
Warning 1287 'TIMESTAMP(10)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
109
Warning 1287 'TIMESTAMP(12)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
110
Warning 1287 'TIMESTAMP(14)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead
111
insert t1 values (0,0,0,0,0,0,0),
112
("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
113
"1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59",
114
"1997-12-31 23:47:59");
116
t2 t4 t6 t8 t10 t12 t14
117
0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
118
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
120
t2 t4 t6 t8 t10 t12 t14
121
0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00
122
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
124
create table t1 (ix timestamp);
125
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
127
Warning 1265 Data truncated for column 'ix' at row 2
128
Warning 1265 Data truncated for column 'ix' at row 3
129
Warning 1265 Data truncated for column 'ix' at row 4
130
Warning 1265 Data truncated for column 'ix' at row 5
131
Warning 1265 Data truncated for column 'ix' at row 6
132
Warning 1265 Data truncated for column 'ix' at row 7
133
Warning 1265 Data truncated for column 'ix' at row 8
145
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
147
Warning 1265 Data truncated for column 'ix' at row 2
148
Warning 1265 Data truncated for column 'ix' at row 3
149
Warning 1265 Data truncated for column 'ix' at row 4
150
Warning 1265 Data truncated for column 'ix' at row 5
151
Warning 1265 Data truncated for column 'ix' at row 6
152
Warning 1265 Data truncated for column 'ix' at row 7
153
Warning 1265 Data truncated for column 'ix' at row 8
165
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
167
Warning 1265 Data truncated for column 'ix' at row 1
168
Warning 1265 Data truncated for column 'ix' at row 2
174
create table t1 (t1 timestamp, t2 timestamp default now());
175
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
176
create table t1 (t1 timestamp, t2 timestamp on update now());
177
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
178
create table t1 (t1 timestamp, t2 timestamp default now() on update now());
179
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
180
create table t1 (t1 timestamp default now(), t2 timestamp on update now());
181
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
182
create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now());
183
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
184
create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp);
185
SET TIMESTAMP=1000000000;
186
insert into t1 values ();
187
SET TIMESTAMP=1000000001;
188
update t1 set t2=now();
189
SET TIMESTAMP=1000000002;
190
insert into t1 (t1,t3) values (default, default);
193
2003-01-01 00:00:00 2001-09-09 04:46:41 0000-00-00 00:00:00
194
2003-01-01 00:00:00 NULL 0000-00-00 00:00:00
195
show create table t1;
197
t1 CREATE TABLE `t1` (
198
`t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00',
199
`t2` datetime DEFAULT NULL,
200
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
201
) ENGINE=MyISAM DEFAULT CHARSET=latin1
202
show columns from t1;
203
Field Type Null Key Default Extra
204
t1 timestamp NO 2003-01-01 00:00:00
206
t3 timestamp NO 0000-00-00 00:00:00
208
create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp);
209
SET TIMESTAMP=1000000002;
210
insert into t1 values ();
211
SET TIMESTAMP=1000000003;
212
update t1 set t2=now();
213
SET TIMESTAMP=1000000003;
214
insert into t1 (t1,t3) values (default, default);
217
2001-09-09 04:46:42 2001-09-09 04:46:43 0000-00-00 00:00:00
218
2001-09-09 04:46:43 NULL 0000-00-00 00:00:00
219
show create table t1;
221
t1 CREATE TABLE `t1` (
222
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
223
`t2` datetime DEFAULT NULL,
224
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
225
) ENGINE=MyISAM DEFAULT CHARSET=latin1
226
show columns from t1;
227
Field Type Null Key Default Extra
228
t1 timestamp NO CURRENT_TIMESTAMP
230
t3 timestamp NO 0000-00-00 00:00:00
232
create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime);
233
SET TIMESTAMP=1000000004;
234
insert into t1 values ();
237
2003-01-01 00:00:00 NULL
238
SET TIMESTAMP=1000000005;
239
update t1 set t2=now();
240
SET TIMESTAMP=1000000005;
241
insert into t1 (t1) values (default);
244
2001-09-09 04:46:45 2001-09-09 04:46:45
245
2003-01-01 00:00:00 NULL
246
show create table t1;
248
t1 CREATE TABLE `t1` (
249
`t1` timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
250
`t2` datetime DEFAULT NULL
251
) ENGINE=MyISAM DEFAULT CHARSET=latin1
252
show columns from t1;
253
Field Type Null Key Default Extra
254
t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP
257
create table t1 (t1 timestamp default now() on update now(), t2 datetime);
258
SET TIMESTAMP=1000000006;
259
insert into t1 values ();
262
2001-09-09 04:46:46 NULL
263
SET TIMESTAMP=1000000007;
264
update t1 set t2=now();
265
SET TIMESTAMP=1000000007;
266
insert into t1 (t1) values (default);
269
2001-09-09 04:46:47 2001-09-09 04:46:47
270
2001-09-09 04:46:47 NULL
271
show create table t1;
273
t1 CREATE TABLE `t1` (
274
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
275
`t2` datetime DEFAULT NULL
276
) ENGINE=MyISAM DEFAULT CHARSET=latin1
277
show columns from t1;
278
Field Type Null Key Default Extra
279
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
282
create table t1 (t1 timestamp, t2 datetime, t3 timestamp);
283
SET TIMESTAMP=1000000007;
284
insert into t1 values ();
287
2001-09-09 04:46:47 NULL 0000-00-00 00:00:00
288
SET TIMESTAMP=1000000008;
289
update t1 set t2=now();
290
SET TIMESTAMP=1000000008;
291
insert into t1 (t1,t3) values (default, default);
294
2001-09-09 04:46:48 2001-09-09 04:46:48 0000-00-00 00:00:00
295
2001-09-09 04:46:48 NULL 0000-00-00 00:00:00
296
show create table t1;
298
t1 CREATE TABLE `t1` (
299
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
300
`t2` datetime DEFAULT NULL,
301
`t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
302
) ENGINE=MyISAM DEFAULT CHARSET=latin1
303
show columns from t1;
304
Field Type Null Key Default Extra
305
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
307
t3 timestamp NO 0000-00-00 00:00:00
309
create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
310
SET TIMESTAMP=1000000009;
311
insert into t1 values ();
314
2001-09-09 04:46:49 NULL
315
SET TIMESTAMP=1000000010;
316
update t1 set t2=now();
317
SET TIMESTAMP=1000000011;
318
insert into t1 (t1) values (default);
321
2001-09-09 04:46:50 2001-09-09 04:46:50
322
2001-09-09 04:46:51 NULL
323
show create table t1;
325
t1 CREATE TABLE `t1` (
326
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
327
`t2` datetime DEFAULT NULL
328
) ENGINE=MyISAM DEFAULT CHARSET=latin1
329
show columns from t1;
330
Field Type Null Key Default Extra
331
t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
334
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
335
SET TIMESTAMP=1000000012;
336
update t1 set t1= '2004-04-02 00:00:00';
339
2004-04-02 00:00:00 2004-04-01 00:00:00
340
update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
343
2004-04-03 00:00:00 2004-04-01 00:00:00
345
create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
346
insert into t1 values (1, '2004-04-01 00:00:00', 10);
347
SET TIMESTAMP=1000000013;
348
replace into t1 set pk = 1, bulk= 20;
351
1 2001-09-09 04:46:53 20
353
create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
354
insert into t1 values (1, '2004-04-01 00:00:00', 10);
355
SET TIMESTAMP=1000000014;
356
replace into t1 set pk = 1, bulk= 20;
359
1 2003-01-01 00:00:00 20
361
create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
362
insert into t1 values (1, '2004-04-01 00:00:00', 10);
363
SET TIMESTAMP=1000000015;
364
replace into t1 set pk = 1, bulk= 20;
367
1 2001-09-09 04:46:55 20
369
create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
370
insert into t1 values ('2004-04-01 00:00:00');
371
SET TIMESTAMP=1000000016;
372
alter table t1 add i int default 10;
375
2004-04-01 00:00:00 10
377
create table t1 (a timestamp null, b timestamp null);
378
show create table t1;
380
t1 CREATE TABLE `t1` (
381
`a` timestamp NULL DEFAULT NULL,
382
`b` timestamp NULL DEFAULT NULL
383
) ENGINE=MyISAM DEFAULT CHARSET=latin1
384
insert into t1 values (NULL, NULL);
385
SET TIMESTAMP=1000000017;
386
insert into t1 values ();
392
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
393
show create table t1;
395
t1 CREATE TABLE `t1` (
396
`a` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
397
`b` timestamp NULL DEFAULT NULL
398
) ENGINE=MyISAM DEFAULT CHARSET=latin1
399
insert into t1 values (NULL, NULL);
400
SET TIMESTAMP=1000000018;
401
insert into t1 values ();
405
2001-09-09 04:46:58 NULL
407
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
408
show create table t1;
410
t1 CREATE TABLE `t1` (
411
`a` timestamp NULL DEFAULT NULL,
412
`b` timestamp NULL DEFAULT '2003-01-01 00:00:00'
413
) ENGINE=MyISAM DEFAULT CHARSET=latin1
414
insert into t1 values (NULL, NULL);
415
insert into t1 values (DEFAULT, DEFAULT);
419
NULL 2003-01-01 00:00:00
421
create table t1 (a bigint, b bigint);
422
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
423
set timestamp=1000000019;
424
alter table t1 modify a timestamp, modify b timestamp;
427
2001-09-09 04:46:59 2001-09-09 04:46:59
428
2003-01-01 00:00:00 2003-01-02 00:00:00
430
create table t1 (a char(2), t timestamp);
431
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
432
('b', '2004-02-01 00:00:00');
433
select max(t) from t1 group by a;
438
set sql_mode='maxdb';
439
create table t1 (a timestamp, b timestamp(19));
440
show create table t1;
442
t1 CREATE TABLE "t1" (
443
"a" datetime DEFAULT NULL,
444
"b" datetime DEFAULT NULL
448
create table t1 (a int auto_increment primary key, b int, c timestamp);
449
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
450
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
453
1 0 2001-01-01 01:01:01
454
2 0 2002-02-02 02:02:02
455
3 0 2003-03-03 03:03:03
456
update t1 set b = 2, c = c where a = 2;
459
1 0 2001-01-01 01:01:01
460
2 2 2002-02-02 02:02:02
461
3 0 2003-03-03 03:03:03
462
insert into t1 (a) values (4);
465
1 0 2001-01-01 01:01:01
466
2 2 2002-02-02 02:02:02
467
3 0 2003-03-03 03:03:03
468
4 NULL 2001-09-09 04:46:59
469
update t1 set c = '2004-04-04 04:04:04' where a = 4;
472
1 0 2001-01-01 01:01:01
473
2 2 2002-02-02 02:02:02
474
3 0 2003-03-03 03:03:03
475
4 NULL 2004-04-04 04:04:04
476
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
479
1 0 2001-01-01 01:01:01
480
2 2 2002-02-02 02:02:02
481
3 3 2003-03-03 03:03:03
482
4 NULL 2004-04-04 04:04:04
483
5 NULL 2001-09-09 04:46:59
484
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
485
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
488
1 0 2001-01-01 01:01:01
489
2 2 2002-02-02 02:02:02
490
3 3 2003-03-03 03:03:03
491
4 4 2001-09-09 04:46:59
492
5 NULL 2001-09-09 04:46:59
493
6 NULL 2006-06-06 06:06:06
496
set time_zone= @@global.time_zone;
498
`id` int(11) NOT NULL auto_increment,
499
`username` varchar(80) NOT NULL default '',
500
`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
502
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
504
Field Type Null Key Default Extra
505
id int(11) NO PRI NULL auto_increment
506
username varchar(80) NO
507
posted_on timestamp NO 0000-00-00 00:00:00
508
select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
512
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
513
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
515
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
516
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
517
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
518
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
519
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
520
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
521
SELECT f1,f2-f3 FROM t1;