|
1
by brian
clean slate |
1 |
drop table if exists t1, t2;
|
2 |
drop function if exists f1;
|
|
3 |
create table t1 (ts timestamp);
|
|
4 |
set time_zone='+00:00';
|
|
5 |
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
|
|
6 |
unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp())
|
|
7 |
0
|
|
8 |
insert into t1 (ts) values ('2003-03-30 02:30:00');
|
|
9 |
set time_zone='+10:30';
|
|
10 |
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
|
|
11 |
unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp())
|
|
12 |
-37800
|
|
13 |
insert into t1 (ts) values ('2003-03-30 02:30:00');
|
|
14 |
set time_zone='-10:00';
|
|
15 |
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
|
|
16 |
unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp())
|
|
17 |
36000
|
|
18 |
insert into t1 (ts) values ('2003-03-30 02:30:00');
|
|
19 |
select * from t1;
|
|
20 |
ts
|
|
21 |
2003-03-29 16:30:00
|
|
22 |
2003-03-29 06:00:00
|
|
23 |
2003-03-30 02:30:00
|
|
24 |
drop table t1;
|
|
25 |
select Name from mysql.time_zone_name where Name in
|
|
26 |
('UTC','Universal','MET','Europe/Moscow','leap/Europe/Moscow');
|
|
27 |
Name
|
|
28 |
Europe/Moscow
|
|
29 |
leap/Europe/Moscow
|
|
30 |
MET
|
|
31 |
Universal
|
|
32 |
UTC
|
|
33 |
create table t1 (i int, ts timestamp);
|
|
34 |
set time_zone='MET';
|
|
35 |
insert into t1 (i, ts) values
|
|
36 |
(unix_timestamp('2003-03-01 00:00:00'),'2003-03-01 00:00:00');
|
|
37 |
insert into t1 (i, ts) values
|
|
38 |
(unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'),
|
|
39 |
(unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'),
|
|
40 |
(unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00');
|
|
41 |
Warnings:
|
|
42 |
Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
|
|
43 |
insert into t1 (i, ts) values
|
|
44 |
(unix_timestamp(20030330015959),20030330015959),
|
|
45 |
(unix_timestamp(20030330023000),20030330023000),
|
|
46 |
(unix_timestamp(20030330030000),20030330030000);
|
|
47 |
Warnings:
|
|
48 |
Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
|
|
49 |
insert into t1 (i, ts) values
|
|
50 |
(unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
|
|
51 |
insert into t1 (i, ts) values
|
|
52 |
(unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'),
|
|
53 |
(unix_timestamp('2003-10-26 02:00:00'),'2003-10-26 02:00:00'),
|
|
54 |
(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59'),
|
|
55 |
(unix_timestamp('2003-10-26 04:00:00'),'2003-10-26 04:00:00'),
|
|
56 |
(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59');
|
|
57 |
set time_zone='UTC';
|
|
58 |
select * from t1;
|
|
59 |
i ts
|
|
60 |
1046473200 2003-02-28 23:00:00
|
|
61 |
1048985999 2003-03-30 00:59:59
|
|
62 |
1048986000 2003-03-30 01:00:00
|
|
63 |
1048986000 2003-03-30 01:00:00
|
|
64 |
1048985999 2003-03-30 00:59:59
|
|
65 |
1048986000 2003-03-30 01:00:00
|
|
66 |
1048986000 2003-03-30 01:00:00
|
|
67 |
1051740000 2003-04-30 22:00:00
|
|
68 |
1067122800 2003-10-25 23:00:00
|
|
69 |
1067126400 2003-10-26 00:00:00
|
|
70 |
1067129999 2003-10-26 00:59:59
|
|
71 |
1067137200 2003-10-26 03:00:00
|
|
72 |
1067129999 2003-10-26 00:59:59
|
|
73 |
truncate table t1;
|
|
74 |
set time_zone='Europe/Moscow';
|
|
75 |
insert into t1 (i, ts) values
|
|
76 |
(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
|
|
77 |
(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
|
|
78 |
(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
|
|
79 |
(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
|
|
80 |
Warnings:
|
|
81 |
Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
|
|
82 |
select * from t1;
|
|
83 |
i ts
|
|
84 |
1072904400 2004-01-01 00:00:00
|
|
85 |
1080428400 2004-03-28 03:00:00
|
|
86 |
1091304000 2003-08-01 00:00:00
|
|
87 |
1099175400 2004-10-31 02:30:00
|
|
88 |
truncate table t1;
|
|
89 |
set time_zone='leap/Europe/Moscow';
|
|
90 |
insert into t1 (i, ts) values
|
|
91 |
(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
|
|
92 |
(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
|
|
93 |
(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
|
|
94 |
(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
|
|
95 |
Warnings:
|
|
96 |
Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
|
|
97 |
select * from t1;
|
|
98 |
i ts
|
|
99 |
1072904422 2004-01-01 00:00:00
|
|
100 |
1080428422 2004-03-28 03:00:00
|
|
101 |
1091304022 2003-08-01 00:00:00
|
|
102 |
1099175422 2004-10-31 02:30:00
|
|
103 |
truncate table t1;
|
|
104 |
insert into t1 (i, ts) values
|
|
105 |
(unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'),
|
|
106 |
(unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00');
|
|
107 |
select * from t1;
|
|
108 |
i ts
|
|
109 |
362793608 1981-07-01 03:59:59
|
|
110 |
362793610 1981-07-01 04:00:00
|
|
111 |
select from_unixtime(362793609);
|
|
112 |
from_unixtime(362793609)
|
|
113 |
1981-07-01 03:59:60
|
|
114 |
drop table t1;
|
|
115 |
create table t1 (ts timestamp);
|
|
116 |
set time_zone='UTC';
|
|
117 |
insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'),
|
|
118 |
('1970-01-01 00:00:00'),('1970-01-01 00:00:01'),
|
|
119 |
('2038-01-19 03:14:07'),('2038-01-19 03:14:08');
|
|
120 |
Warnings:
|
|
121 |
Warning 1264 Out of range value for column 'ts' at row 2
|
|
122 |
Warning 1264 Out of range value for column 'ts' at row 3
|
|
123 |
Warning 1264 Out of range value for column 'ts' at row 6
|
|
124 |
select * from t1;
|
|
125 |
ts
|
|
126 |
0000-00-00 00:00:00
|
|
127 |
0000-00-00 00:00:00
|
|
128 |
0000-00-00 00:00:00
|
|
129 |
1970-01-01 00:00:01
|
|
130 |
2038-01-19 03:14:07
|
|
131 |
0000-00-00 00:00:00
|
|
132 |
truncate table t1;
|
|
133 |
set time_zone='MET';
|
|
134 |
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'),
|
|
135 |
('1970-01-01 01:00:00'),('1970-01-01 01:00:01'),
|
|
136 |
('2038-01-19 04:14:07'),('2038-01-19 04:14:08');
|
|
137 |
Warnings:
|
|
138 |
Warning 1264 Out of range value for column 'ts' at row 2
|
|
139 |
Warning 1264 Out of range value for column 'ts' at row 3
|
|
140 |
Warning 1264 Out of range value for column 'ts' at row 6
|
|
141 |
select * from t1;
|
|
142 |
ts
|
|
143 |
0000-00-00 00:00:00
|
|
144 |
0000-00-00 00:00:00
|
|
145 |
0000-00-00 00:00:00
|
|
146 |
1970-01-01 01:00:01
|
|
147 |
2038-01-19 04:14:07
|
|
148 |
0000-00-00 00:00:00
|
|
149 |
truncate table t1;
|
|
150 |
set time_zone='+01:30';
|
|
151 |
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'),
|
|
152 |
('1970-01-01 01:30:00'),('1970-01-01 01:30:01'),
|
|
153 |
('2038-01-19 04:44:07'),('2038-01-19 04:44:08');
|
|
154 |
Warnings:
|
|
155 |
Warning 1264 Out of range value for column 'ts' at row 2
|
|
156 |
Warning 1264 Out of range value for column 'ts' at row 3
|
|
157 |
Warning 1264 Out of range value for column 'ts' at row 6
|
|
158 |
select * from t1;
|
|
159 |
ts
|
|
160 |
0000-00-00 00:00:00
|
|
161 |
0000-00-00 00:00:00
|
|
162 |
0000-00-00 00:00:00
|
|
163 |
1970-01-01 01:30:01
|
|
164 |
2038-01-19 04:44:07
|
|
165 |
0000-00-00 00:00:00
|
|
166 |
drop table t1;
|
|
167 |
show variables like 'time_zone';
|
|
168 |
Variable_name Value
|
|
169 |
time_zone +01:30
|
|
170 |
set time_zone = default;
|
|
171 |
show variables like 'time_zone';
|
|
172 |
Variable_name Value
|
|
173 |
time_zone SYSTEM
|
|
174 |
set time_zone= '0';
|
|
175 |
ERROR HY000: Unknown or incorrect time zone: '0'
|
|
176 |
set time_zone= '0:0';
|
|
177 |
ERROR HY000: Unknown or incorrect time zone: '0:0'
|
|
178 |
set time_zone= '-20:00';
|
|
179 |
ERROR HY000: Unknown or incorrect time zone: '-20:00'
|
|
180 |
set time_zone= '+20:00';
|
|
181 |
ERROR HY000: Unknown or incorrect time zone: '+20:00'
|
|
182 |
set time_zone= 'Some/Unknown/Time/Zone';
|
|
183 |
ERROR HY000: Unknown or incorrect time zone: 'Some/Unknown/Time/Zone'
|
|
184 |
select convert_tz(now(),'UTC', 'Universal') = now();
|
|
185 |
convert_tz(now(),'UTC', 'Universal') = now()
|
|
186 |
1
|
|
187 |
select convert_tz(now(),'utc', 'UTC') = now();
|
|
188 |
convert_tz(now(),'utc', 'UTC') = now()
|
|
189 |
1
|
|
190 |
select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC');
|
|
191 |
convert_tz('1917-11-07 12:00:00', 'MET', 'UTC')
|
|
192 |
1917-11-07 12:00:00
|
|
193 |
select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC');
|
|
194 |
convert_tz('1970-01-01 01:00:00', 'MET', 'UTC')
|
|
195 |
1970-01-01 01:00:00
|
|
196 |
select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC');
|
|
197 |
convert_tz('1970-01-01 01:00:01', 'MET', 'UTC')
|
|
198 |
1970-01-01 00:00:01
|
|
199 |
select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
|
|
200 |
convert_tz('2003-03-01 00:00:00', 'MET', 'UTC')
|
|
201 |
2003-02-28 23:00:00
|
|
202 |
select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
|
|
203 |
convert_tz('2003-03-30 01:59:59', 'MET', 'UTC')
|
|
204 |
2003-03-30 00:59:59
|
|
205 |
select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
|
|
206 |
convert_tz('2003-03-30 02:30:00', 'MET', 'UTC')
|
|
207 |
2003-03-30 01:00:00
|
|
208 |
select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
|
|
209 |
convert_tz('2003-03-30 03:00:00', 'MET', 'UTC')
|
|
210 |
2003-03-30 01:00:00
|
|
211 |
select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
|
|
212 |
convert_tz('2003-05-01 00:00:00', 'MET', 'UTC')
|
|
213 |
2003-04-30 22:00:00
|
|
214 |
select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
|
|
215 |
convert_tz('2003-10-26 01:00:00', 'MET', 'UTC')
|
|
216 |
2003-10-25 23:00:00
|
|
217 |
select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
|
|
218 |
convert_tz('2003-10-26 02:00:00', 'MET', 'UTC')
|
|
219 |
2003-10-26 00:00:00
|
|
220 |
select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
|
|
221 |
convert_tz('2003-10-26 02:59:59', 'MET', 'UTC')
|
|
222 |
2003-10-26 00:59:59
|
|
223 |
select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
|
|
224 |
convert_tz('2003-10-26 04:00:00', 'MET', 'UTC')
|
|
225 |
2003-10-26 03:00:00
|
|
226 |
select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
|
|
227 |
convert_tz('2038-01-19 04:14:07', 'MET', 'UTC')
|
|
228 |
2038-01-19 03:14:07
|
|
229 |
select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
|
|
230 |
convert_tz('2038-01-19 04:14:08', 'MET', 'UTC')
|
|
231 |
2038-01-19 04:14:08
|
|
232 |
select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
|
|
233 |
convert_tz('2103-01-01 04:00:00', 'MET', 'UTC')
|
|
234 |
2103-01-01 04:00:00
|
|
235 |
create table t1 (tz varchar(3));
|
|
236 |
insert into t1 (tz) values ('MET'), ('UTC');
|
|
237 |
select tz, convert_tz('2003-12-31 00:00:00',tz,'UTC'), convert_tz('2003-12-31 00:00:00','UTC',tz) from t1 order by tz;
|
|
238 |
tz convert_tz('2003-12-31 00:00:00',tz,'UTC') convert_tz('2003-12-31 00:00:00','UTC',tz)
|
|
239 |
MET 2003-12-30 23:00:00 2003-12-31 01:00:00
|
|
240 |
UTC 2003-12-31 00:00:00 2003-12-31 00:00:00
|
|
241 |
drop table t1;
|
|
242 |
select convert_tz('2003-12-31 04:00:00', NULL, 'UTC');
|
|
243 |
convert_tz('2003-12-31 04:00:00', NULL, 'UTC')
|
|
244 |
NULL
|
|
245 |
select convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC');
|
|
246 |
convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC')
|
|
247 |
NULL
|
|
248 |
select convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone');
|
|
249 |
convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone')
|
|
250 |
NULL
|
|
251 |
select convert_tz('2003-12-31 04:00:00', 'MET', NULL);
|
|
252 |
convert_tz('2003-12-31 04:00:00', 'MET', NULL)
|
|
253 |
NULL
|
|
254 |
select convert_tz( NULL, 'MET', 'UTC');
|
|
255 |
convert_tz( NULL, 'MET', 'UTC')
|
|
256 |
NULL
|
|
257 |
create table t1 (ts timestamp);
|
|
258 |
set timestamp=1000000000;
|
|
259 |
insert into t1 (ts) values (now());
|
|
260 |
select convert_tz(ts, @@time_zone, 'Japan') from t1;
|
|
261 |
convert_tz(ts, @@time_zone, 'Japan')
|
|
262 |
2001-09-09 10:46:40
|
|
263 |
drop table t1;
|
|
264 |
select convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone) from (select 'UTC' as custTimeZone) as tmp;
|
|
265 |
convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone)
|
|
266 |
2005-01-14 17:00:00
|
|
267 |
create table t1 select convert_tz(NULL, NULL, NULL);
|
|
268 |
select * from t1;
|
|
269 |
convert_tz(NULL, NULL, NULL)
|
|
270 |
NULL
|
|
271 |
drop table t1;
|
|
272 |
SET GLOBAL log_bin_trust_function_creators = 1;
|
|
273 |
create table t1 (ldt datetime, udt datetime);
|
|
274 |
create function f1(i datetime) returns datetime
|
|
275 |
return convert_tz(i, 'UTC', 'Europe/Moscow');
|
|
276 |
create trigger t1_bi before insert on t1 for each row
|
|
277 |
set new.udt:= convert_tz(new.ldt, 'Europe/Moscow', 'UTC');
|
|
278 |
insert into t1 (ldt) values ('2006-04-19 16:30:00');
|
|
279 |
select * from t1;
|
|
280 |
ldt udt
|
|
281 |
2006-04-19 16:30:00 2006-04-19 12:30:00
|
|
282 |
select ldt, f1(udt) as ldt2 from t1;
|
|
283 |
ldt ldt2
|
|
284 |
2006-04-19 16:30:00 2006-04-19 16:30:00
|
|
285 |
drop table t1;
|
|
286 |
drop function f1;
|
|
287 |
SET GLOBAL log_bin_trust_function_creators = 0;
|
|
288 |
DROP TABLE IF EXISTS t1;
|
|
289 |
CREATE TABLE t1 (t TIMESTAMP);
|
|
290 |
INSERT INTO t1 VALUES (NULL), (NULL);
|
|
291 |
LOCK TABLES t1 WRITE;
|
|
292 |
SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
|
|
293 |
CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL
|
|
294 |
0
|
|
295 |
UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
|
|
296 |
UNLOCK TABLES;
|
|
297 |
DROP TABLE t1;
|
|
298 |
End of 5.1 tests
|