drop table if exists t1,t2;
set time_zone="+03:00";
CREATE TABLE t1 (a int, t timestamp);
CREATE TABLE t2 (a int, t datetime);
SET TIMESTAMP=1234;
insert into t1 values(1,NULL);
insert into t1 values(2,"2002-03-03");
SET TIMESTAMP=1235;
insert into t1 values(3,NULL);
SET TIMESTAMP=1236;
insert into t1 (a) values(4);
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
SET TIMESTAMP=1237;
insert into t1 select * from t2;
SET TIMESTAMP=1238;
insert into t1 (a) select a+1 from t2 where a=8;
select * from t1;
a	t
1	1970-01-01 03:20:34
2	2002-03-03 00:00:00
3	1970-01-01 03:20:35
4	1970-01-01 03:20:36
5	2002-03-04 00:00:00
6	1970-01-01 03:20:37
7	2002-03-05 00:00:00
8	0000-00-00 00:00:00
9	1970-01-01 03:20:38
drop table t1,t2;
SET TIMESTAMP=1234;
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
UPDATE t1 SET value="my value" WHERE id="myKey";
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
UPDATE t1 SET id="myKey" WHERE value="my value";
SELECT stamp FROM t1 WHERE id="myKey";
stamp
1999-04-02 00:00:00
drop table t1;
create table t1 (a timestamp);
insert into t1 values (now());
select date_format(a,"%Y %y"),year(a),year(now()) from t1;
date_format(a,"%Y %y")	year(a)	year(now())
1970 70	1970	1970
drop table t1;
create table t1 (ix timestamp);
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
select ix+0 from t1;
ix+0
19991101000000
19990102030405
19990630232922
19990601000000
19990930232922
19990531232922
19990501000000
19991101000000
19990501000000
delete from t1;
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000");
select ix+0 from t1;
ix+0
19991101000000
19990102030405
19990630232922
19990601000000
drop table t1;
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
SELECT * FROM t1;
date	date_time	time_stamp
1998-12-31	1998-12-31 23:59:59	1998-12-31 23:59:59
1999-01-01	1999-01-01 00:00:00	1999-01-01 00:00:00
1999-09-09	1999-09-09 23:59:59	1999-09-09 23:59:59
2000-01-01	2000-01-01 00:00:00	2000-01-01 00:00:00
2000-02-28	2000-02-28 00:00:00	2000-02-28 00:00:00
2000-02-29	2000-02-29 00:00:00	2000-02-29 00:00:00
2000-03-01	2000-03-01 00:00:00	2000-03-01 00:00:00
2000-12-31	2000-12-31 23:59:59	2000-12-31 23:59:59
2001-01-01	2001-01-01 00:00:00	2001-01-01 00:00:00
2004-12-31	2004-12-31 23:59:59	2004-12-31 23:59:59
2005-01-01	2005-01-01 00:00:00	2005-01-01 00:00:00
2030-01-01	2030-01-01 00:00:00	2030-01-01 00:00:00
drop table t1;
create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6),
t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
t14 timestamp(14));
Warnings:
Warning	1287	'TIMESTAMP(2)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(4)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(6)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(8)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(10)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(12)' is deprecated; use 'TIMESTAMP' instead
Warning	1287	'TIMESTAMP(14)' is deprecated; use 'TIMESTAMP' instead
insert t1 values (0,0,0,0,0,0,0),
("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");
select * from t1;
t2	t4	t6	t8	t10	t12	t14
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
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
select * from t1;
t2	t4	t6	t8	t10	t12	t14
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
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
drop table t1;
create table t1 (ix timestamp);
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
Warnings:
Warning	1265	Data truncated for column 'ix' at row 2
Warning	1265	Data truncated for column 'ix' at row 3
Warning	1265	Data truncated for column 'ix' at row 4
Warning	1265	Data truncated for column 'ix' at row 5
Warning	1265	Data truncated for column 'ix' at row 6
Warning	1265	Data truncated for column 'ix' at row 7
Warning	1265	Data truncated for column 'ix' at row 8
select ix+0 from t1;
ix+0
0
0
0
0
0
0
0
0
delete from t1;
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
Warnings:
Warning	1265	Data truncated for column 'ix' at row 2
Warning	1265	Data truncated for column 'ix' at row 3
Warning	1265	Data truncated for column 'ix' at row 4
Warning	1265	Data truncated for column 'ix' at row 5
Warning	1265	Data truncated for column 'ix' at row 6
Warning	1265	Data truncated for column 'ix' at row 7
Warning	1265	Data truncated for column 'ix' at row 8
select ix+0 from t1;
ix+0
0
0
0
0
0
0
0
0
delete from t1;
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
Warnings:
Warning	1265	Data truncated for column 'ix' at row 1
Warning	1265	Data truncated for column 'ix' at row 2
select ix+0 from t1;
ix+0
0
20030101000000
drop table t1;
create table t1 (t1 timestamp, t2 timestamp default now());
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
create table t1 (t1 timestamp, t2 timestamp on update now());
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
create table t1 (t1 timestamp, t2 timestamp default now() on update now());
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
create table t1 (t1 timestamp default now(), t2 timestamp on update now());
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now());
ERROR HY000: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp);
SET TIMESTAMP=1000000000;
insert into t1 values ();
SET TIMESTAMP=1000000001;
update t1 set t2=now();
SET TIMESTAMP=1000000002;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2003-01-01 00:00:00	2001-09-09 04:46:41	0000-00-00 00:00:00
2003-01-01 00:00:00	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default '2003-01-01 00:00:00',
  `t2` datetime default NULL,
  `t3` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		2003-01-01 00:00:00	
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp);
SET TIMESTAMP=1000000002;
insert into t1 values ();
SET TIMESTAMP=1000000003;
update t1 set t2=now();
SET TIMESTAMP=1000000003;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2001-09-09 04:46:42	2001-09-09 04:46:43	0000-00-00 00:00:00
2001-09-09 04:46:43	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `t2` datetime default NULL,
  `t3` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime);
SET TIMESTAMP=1000000004;
insert into t1 values ();
select * from t1;
t1	t2
2003-01-01 00:00:00	NULL
SET TIMESTAMP=1000000005;
update t1 set t2=now();
SET TIMESTAMP=1000000005;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:45	2001-09-09 04:46:45
2003-01-01 00:00:00	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default '2003-01-01 00:00:00' on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		2003-01-01 00:00:00	
t2	datetime	YES		NULL	
drop table t1;
create table t1 (t1 timestamp default now() on update now(), t2 datetime);
SET TIMESTAMP=1000000006;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:46	NULL
SET TIMESTAMP=1000000007;
update t1 set t2=now();
SET TIMESTAMP=1000000007;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:47	2001-09-09 04:46:47
2001-09-09 04:46:47	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
drop table t1;
create table t1 (t1 timestamp, t2 datetime, t3 timestamp);
SET TIMESTAMP=1000000007;
insert into t1 values ();
select * from t1;
t1	t2	t3
2001-09-09 04:46:47	NULL	0000-00-00 00:00:00
SET TIMESTAMP=1000000008;
update t1 set t2=now();
SET TIMESTAMP=1000000008;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2001-09-09 04:46:48	2001-09-09 04:46:48	0000-00-00 00:00:00
2001-09-09 04:46:48	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL,
  `t3` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
t3	timestamp	NO		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
SET TIMESTAMP=1000000009;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:49	NULL
SET TIMESTAMP=1000000010;
update t1 set t2=now();
SET TIMESTAMP=1000000011;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:50	2001-09-09 04:46:50
2001-09-09 04:46:51	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	NO		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
delete from t1;
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
SET TIMESTAMP=1000000012;
update t1 set t1= '2004-04-02 00:00:00';
select * from t1;
t1	t2
2004-04-02 00:00:00	2004-04-01 00:00:00
update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
select * from t1;
t1	t2
2004-04-03 00:00:00	2004-04-01 00:00:00
drop table t1;
create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000013;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:53	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000014;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2003-01-01 00:00:00	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000015;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:55	20
drop table t1;
create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
insert into t1 values ('2004-04-01 00:00:00');
SET TIMESTAMP=1000000016;
alter table t1 add i int default 10;
select * from t1;
t1	i
2004-04-01 00:00:00	10
drop table t1;
create table t1 (a timestamp null, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default NULL,
  `b` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000017;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
NULL	NULL
drop table t1;
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `b` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000018;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
2001-09-09 04:46:58	NULL
drop table t1;
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default NULL,
  `b` timestamp NULL default '2003-01-01 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
insert into t1 values (DEFAULT, DEFAULT);
select * from t1;
a	b
NULL	NULL
NULL	2003-01-01 00:00:00
drop table t1;
create table t1 (a bigint, b bigint);
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
set timestamp=1000000019;
alter table t1 modify a timestamp, modify b timestamp;
select * from t1;
a	b
2001-09-09 04:46:59	2001-09-09 04:46:59
2003-01-01 00:00:00	2003-01-02 00:00:00
drop table t1;
create table t1 (a char(2), t timestamp);
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
('b', '2004-02-01 00:00:00');
select max(t) from t1 group by a;
max(t)
2004-01-01 01:00:00
2004-02-01 00:00:00
drop table t1;
set sql_mode='maxdb';
create table t1 (a timestamp, b timestamp(19));
show create table t1;
Table	Create Table
t1	CREATE TABLE "t1" (
  "a" datetime default NULL,
  "b" datetime default NULL
)
set sql_mode='';
drop table t1;
create table t1 (a int auto_increment primary key, b int, c timestamp);
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	0	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
update t1 set b = 2, c = c where a = 2;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
insert into t1 (a) values (4);
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2001-09-09 04:46:59
update t1 set c = '2004-04-04 04:04:04' where a = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
5	NULL	2001-09-09 04:46:59
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	4	2001-09-09 04:46:59
5	NULL	2001-09-09 04:46:59
6	NULL	2006-06-06 06:06:06
drop table t1;
End of 4.1 tests
set time_zone= @@global.time_zone;
CREATE TABLE t1 (
`id` int(11) NOT NULL auto_increment,
`username` varchar(80) NOT NULL default '',
`posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
show fields from t1;
Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
username	varchar(80)	NO			
posted_on	timestamp	NO		0000-00-00 00:00:00	
select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
is_nullable
NO
drop table t1;
CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
f3 TIMESTAMP);
INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
SELECT f1,f2-f3 FROM t1;
f1	f2-f3
1	0
2	0
3	0
4	0
5	0
DROP TABLE t1;
End of 5.0 tests
