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
|
#
# Test timestamp
#
drop table if exists t1,t2;
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;
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";
UPDATE t1 SET value="my value" WHERE id="myKey";
SELECT stamp FROM t1 WHERE id="myKey";
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;
drop table t1;
create table t1 (ix timestamp);
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101);
select * from t1;
delete from t1;
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101");
select * from t1;
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);
# The following will get you an different answer on 64 bit machines
#INSERT INTO t1 VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
SELECT * FROM t1;
drop table t1;
show variables like 'new';
create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6),
t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
t14 timestamp(14));
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;
set new=1;
select * from t1;
drop table t1;
set new=0;
#
# Bug #1885, bug #2539.
# Not perfect but still sensible attitude towards defaults for TIMESTAMP
# We will ignore default value for first TIMESTAMP column.
#
create table t1 (t1 timestamp default '2003-01-01 00:00:00',
t2 timestamp default '2003-01-01 00:00:00');
set TIMESTAMP=1000000000;
insert into t1 values();
select * from t1;
show create table t1;
show columns from t1;
show columns from t1 like 't2';
create table t2 (select * from t1);
show create table t2;
# Ugly, but we can't do anything about this in 4.0
alter table t1 add column t0 timestamp first;
show create table t1;
drop table t1,t2;
#
# Test for bug 2464, DEFAULT keyword in INSERT statement should return
# default value for column.
#
create table t1 (ts1 timestamp, ts2 timestamp);
set TIMESTAMP=1000000000;
insert into t1 values ();
insert into t1 values (DEFAULT, DEFAULT);
select * from t1;
drop table t1;
#
# Test for bug #4491, TIMESTAMP(19) should be possible to create and not
# only read in 4.0
#
create table t1 (ts timestamp(19));
show create table t1;
set TIMESTAMP=1000000000;
insert into t1 values ();
select * from t1;
drop table t1;
#
# Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
# using GROUP BY in @@new=1 mode.
#
set new=1;
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;
drop table t1;
#
# More --new mode tests
# Both columns created before and during alter should have same length.
#
create table t1 (ts1 timestamp);
show create table t1;
alter table t1 add ts2 timestamp;
set new=0;
show create table t1;
drop table t1;
# Selecting from table in --new mode should not affect further selects.
create table t1 (ts1 timestamp);
insert into t1 values ('2004-01-01 00:00:00'), ('2004-01-01 01:00:00');
select * from t1;
set new=1;
select * from t1;
set new=0;
select * from t1;
drop table t1;
|
