Debian Codegraph @

mysql-test/suite/engines/iuds/r/update_delete_calendar.result

raw
1 DROP TABLE IF EXISTS t1,t2;
2 CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2));
3 INSERT INTO t1 VALUE('1998-01-01','10-01-01',1),('2001-01-01','70-01-01',2),('2001-01-03','2001-01-04 09:34:00',3);
4 Warnings:
5 Note 1265 Data truncated for column 'c2' at row 3
6 SELECT * FROM t1;
7 c1 c2 c3
8 1998-01-01 2010-01-01 1
9 2001-01-01 1970-01-01 2
10 2001-01-03 2001-01-04 3
11 UPDATE t1 SET c1='69-02-02' WHERE c2='10-01-01';
12 SELECT year(c1),month(c1),day(c1),year(c2) FROM t1 WHERE c2='10-01-01';
13 year(c1) month(c1) day(c1) year(c2)
14 2069 2 2 2010
15 UPDATE t1 SET c1='1969-02-02',c2='1910-02-02' WHERE c2='10-01-01';
16 SELECT * FROM t1 WHERE c1='1969-02-02' AND c2='1910-02-02';
17 c1 c2 c3
18 1969-02-02 1910-02-02 1
19 UPDATE t1 SET c1='01-02-02',c2='2070-02-02' WHERE c2='70-01-01';
20 SELECT year(c1),c2 FROM t1 WHERE c1='01-02-02' AND c2='2070-02-02';
21 year(c1) c2
22 2001 2070-02-02
23 UPDATE t1 SET c1='2001-01-03 08:30:00',c2='2001-01-05' WHERE c2='2001-01-04';
24 Warnings:
25 Note 1265 Data truncated for column 'c1' at row 1
26 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='2001-01-05';
27 c1 hour(c1) minute(c1) second(c1)
28 2001-01-03 0 0 0
29 INSERT INTO t1 VALUES('2009-02-25','09-02-27',4);
30 SET TIMESTAMP=1235553613;
31 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
32 Warnings:
33 Note 1265 Data truncated for column 'c2' at row 1
34 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURDATE();
35 Warnings:
36 Note 1265 Data truncated for column 'c1' at row 1
37 SELECT * FROM t1;
38 c1 c2 c3
39 1969-02-02 1910-02-02 1
40 2001-01-03 2001-01-05 3
41 2001-02-02 2070-02-02 2
42 2009-02-26 2009-02-25 4
43 INSERT INTO t1 VALUES('2001-01-06','2001-01-07',5),('2001-01-08',NULL,6);
44 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06';
45 SELECT * FROM t1 WHERE c2 IS NULL;
46 c1 c2 c3
47 2001-01-06 NULL 5
48 2001-01-08 NULL 6
49 UPDATE t1 SET c2='2001-01-10' WHERE c2 IS NULL;
50 SELECT * FROM t1 WHERE c2='2001-01-10';
51 c1 c2 c3
52 2001-01-06 2001-01-10 5
53 2001-01-08 2001-01-10 6
54 INSERT INTO t1 VALUES('2001-01-11','2001-01-11',7),('2001-01-12','2001-01-12',8),('2001-01-13','2001-01-13',9),('2001-01-14','2001-01-14',10),('2001-01-15','2001-01-15',11),('2001-01-16','2001-01-16',12);
55 UPDATE t1 SET c1='10:45:15',c2='2009-01-32' WHERE c1='2001-01-11';
56 Warnings:
57 Warning 1265 Data truncated for column 'c1' at row 1
58 Warning 1265 Data truncated for column 'c2' at row 1
59 UPDATE t1 SET c2='2009-02-30' WHERE c2='2001-01-12';
60 Warnings:
61 Warning 1265 Data truncated for column 'c2' at row 1
62 UPDATE t1 SET c2='2009-04-31' WHERE c2='2001-01-13';
63 Warnings:
64 Warning 1265 Data truncated for column 'c2' at row 1
65 UPDATE t1 SET c2='2009-06-31' WHERE c2='2001-01-14';
66 Warnings:
67 Warning 1265 Data truncated for column 'c2' at row 1
68 UPDATE t1 SET c2='2009-09-31' WHERE c2='2001-01-15';
69 Warnings:
70 Warning 1265 Data truncated for column 'c2' at row 1
71 SELECT count(*) FROM t1 WHERE c2='10:45:15' OR c2='2009-01-32' OR c2='2009-02-30' OR c2='2009-04-31' OR c2='2009-06-31' OR c2='2009-09-31' /* returns 0 */;
72 count(*)
73 0
74 UPDATE t1 SET c2='2009-11-31' WHERE c2='2001-01-16';
75 Warnings:
76 Warning 1265 Data truncated for column 'c2' at row 1
77 SELECT * FROM t1 WHERE c2='0000-00-00';
78 c1 c2 c3
79 0000-00-00 0000-00-00 7
80 2001-01-12 0000-00-00 8
81 2001-01-13 0000-00-00 9
82 2001-01-14 0000-00-00 10
83 2001-01-15 0000-00-00 11
84 2001-01-16 0000-00-00 12
85 SET SQL_MODE=ALLOW_INVALID_DATES;
86 INSERT INTO t1 VALUES('2001-01-27','2001-01-28',19);
87 UPDATE t1 SET c1='2009-04-31',c2='2009-02-30' WHERE c3=19;
88 SELECT c1,c2,day(c1),day(c2) FROM t1 WHERE c3=19;
89 c1 c2 day(c1) day(c2)
90 2009-04-31 2009-02-30 31 30
91 SET SQL_MODE=DEFAULT;
92 INSERT INTO t1 VALUES('2001-01-17','0000-00-00',13),('2001-01-18','2001-01-18',14);
93 SET SQL_MODE=TRADITIONAL;
94 SELECT * FROM t1 WHERE c2='0000-00-00';
95 c1 c2 c3
96 0000-00-00 0000-00-00 7
97 2001-01-12 0000-00-00 8
98 2001-01-13 0000-00-00 9
99 2001-01-14 0000-00-00 10
100 2001-01-15 0000-00-00 11
101 2001-01-16 0000-00-00 12
102 2001-01-17 0000-00-00 13
103 Warnings:
104 Warning 1264 Out of range value for column 'c2' at row 1
105 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
106 ERROR 22007: Incorrect date value: '0000-00-00' for column 'c2' at row 1
107 SET SQL_MODE=DEFAULT;
108 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
109 UPDATE t1 SET c2='2001-01-18' WHERE c2='0000-00-00' AND c1='2001-01-17';
110 SELECT * FROM t1 WHERE c1='0000-00-00' OR c2='0000-00-00';
111 c1 c2 c3
112 0000-00-00 0000-00-00 7
113 2001-01-12 0000-00-00 8
114 2001-01-13 0000-00-00 9
115 2001-01-14 0000-00-00 10
116 2001-01-15 0000-00-00 11
117 2001-01-16 0000-00-00 12
118 2001-01-18 0000-00-00 14
119 INSERT INTO t1 VALUES('2001-01-19','2001-01-20',15);
120 UPDATE t1 SET c1='2010-00-01',c2='2010-10-00' WHERE c3=15;
121 SELECT *,month(c1),day(c2) from t1 WHERE c3=15;
122 c1 c2 c3 month(c1) day(c2)
123 2010-00-01 2010-10-00 15 0 0
124 INSERT INTO t1 VALUES('2001-01-21','2001-01-22',16);
125 UPDATE t1 SET c1='1000-01-01',c2='9999-12-31' WHERE c1='2001-01-21';
126 SELECT * FROM t1 WHERE c1='1000-01-01' AND c2='9999-12-31';
127 c1 c2 c3
128 1000-01-01 9999-12-31 16
129 INSERT INTO t1 VALUES('2001-01-23','2001-01-24',17),('2001-01-25','2001-01-26',18);
130 UPDATE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
131 ERROR 23000: Duplicate entry '2001-01-23' for key 'PRIMARY'
132 UPDATE IGNORE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
133 SELECT count(*) FROM t1 WHERE c1='2001-01-23';
134 count(*)
135 1
136 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24' /* updates to 0000-00-00 */;
137 ERROR 23000: Duplicate entry '0000-00-00' for key 'PRIMARY'
138 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24';
139 Warnings:
140 Warning 1048 Column 'c1' cannot be null
141 SELECT * FROM t1 WHERE c1='0000-00-00';
142 c1 c2 c3
143 0000-00-00 0000-00-00 7
144 TRUNCATE TABLE t1;
145 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
146 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
147 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
148 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
149 c1 c2 c3 c1 c2 c3
150 2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
151 UPDATE t1,t2 SET t2.c1='2005-01-06',t1.c1='2005-01-06' WHERE t2.c1=t1.c1;
152 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
153 c1 c2 c3 c1 c2 c3
154 2005-01-06 2001-01-01 1 2005-01-06 2001-01-03 1
155 INSERT INTO t1 VALUES('2002-01-01','2002-01-02',3),('2002-01-04','2002-01-02',4);
156 INSERT INTO t2 VALUES('2002-01-02','2002-01-03',3),('2002-01-03','2002-01-04',4);
157 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
158 c1 c2 c3 c1 c2 c3
159 2002-01-01 2002-01-02 3 2002-01-02 2002-01-03 3
160 2002-01-04 2002-01-02 4 2002-01-02 2002-01-03 3
161 UPDATE t1,t2 SET t2.c1='2002-01-06',t1.c2='2002-01-06' WHERE t2.c1=t1.c2;
162 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06' AND t1.c2='2002-01-06';
163 c1 c2 c3 c1 c2 c3
164 2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
165 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
166 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
167 c1 c2 c3 c1 c2 c3
168 2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
169 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
170 DELETE FROM t1 WHERE c2='2002-01-06' AND c3=3;
171 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
172 c1 c2 c3 c1 c2 c3
173 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
174 DROP TABLE t2;
175 TRUNCATE TABLE t1;
176 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, DROP PRIMARY KEY;
177 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD KEY(c1);
178 UPDATE t1 SET c1='2002-01-07' WHERE c2='2002-01-05';
179 SELECT * FROM t1 WHERE c1='2002-01-07';
180 c1 c2 c3
181 INSERT INTO t1 VALUES('2002-01-08','2002-01-09',4),('2002-01-10','2002-01-11',5),('2002-01-12','2002-01-13',6),('2002-01-14','2002-01-15',7),('2002-01-16','2002-01-17',8),('2002-01-18','2002-01-19',9),('2002-01-20','2002-01-21',10),('2002-01-22','2002-01-23',11),('2002-01-24','2002-01-25',12),('2002-01-26','2002-01-27',13),('2002-01-28','2002-01-29',14),('2002-01-30','2002-01-31',15),('2002-02-01','2002-02-02',16),('2002-02-03','2002-02-04',17),('2002-02-05','2002-02-06',18),('2002-02-07','2002-02-08',19),('2002-02-09','2002-02-10',20);
182 SELECT * FROM t1;
183 c1 c2 c3
184 2002-01-08 2002-01-09 4
185 2002-01-10 2002-01-11 5
186 2002-01-12 2002-01-13 6
187 2002-01-14 2002-01-15 7
188 2002-01-16 2002-01-17 8
189 2002-01-18 2002-01-19 9
190 2002-01-20 2002-01-21 10
191 2002-01-22 2002-01-23 11
192 2002-01-24 2002-01-25 12
193 2002-01-26 2002-01-27 13
194 2002-01-28 2002-01-29 14
195 2002-01-30 2002-01-31 15
196 2002-02-01 2002-02-02 16
197 2002-02-03 2002-02-04 17
198 2002-02-05 2002-02-06 18
199 2002-02-07 2002-02-08 19
200 2002-02-09 2002-02-10 20
201 UPDATE t1 SET c1='2003-01-01' WHERE c2 <> '2002-01-09' ORDER BY c2 LIMIT 2;
202 SELECT * FROM t1 WHERE c2 <> '2002-01-09' ORDER BY c2;
203 c1 c2 c3
204 2003-01-01 2002-01-11 5
205 2003-01-01 2002-01-13 6
206 2002-01-14 2002-01-15 7
207 2002-01-16 2002-01-17 8
208 2002-01-18 2002-01-19 9
209 2002-01-20 2002-01-21 10
210 2002-01-22 2002-01-23 11
211 2002-01-24 2002-01-25 12
212 2002-01-26 2002-01-27 13
213 2002-01-28 2002-01-29 14
214 2002-01-30 2002-01-31 15
215 2002-02-01 2002-02-02 16
216 2002-02-03 2002-02-04 17
217 2002-02-05 2002-02-06 18
218 2002-02-07 2002-02-08 19
219 2002-02-09 2002-02-10 20
220 UPDATE t1 SET c1='2003-01-01' WHERE c2 >= '2002-01-13' ORDER BY c2 DESC LIMIT 2;
221 SELECT * FROM t1 WHERE c2 >= '2002-01-13' ORDER BY c2 DESC;
222 c1 c2 c3
223 2003-01-01 2002-02-10 20
224 2003-01-01 2002-02-08 19
225 2002-02-05 2002-02-06 18
226 2002-02-03 2002-02-04 17
227 2002-02-01 2002-02-02 16
228 2002-01-30 2002-01-31 15
229 2002-01-28 2002-01-29 14
230 2002-01-26 2002-01-27 13
231 2002-01-24 2002-01-25 12
232 2002-01-22 2002-01-23 11
233 2002-01-20 2002-01-21 10
234 2002-01-18 2002-01-19 9
235 2002-01-16 2002-01-17 8
236 2002-01-14 2002-01-15 7
237 2003-01-01 2002-01-13 6
238 UPDATE t1 SET c1='2003-01-01' WHERE c2 <='2002-01-21' ORDER BY c2 LIMIT 2;
239 SELECT * FROM t1 WHERE c2 <='2002-01-21' ORDER BY c2;
240 c1 c2 c3
241 2003-01-01 2002-01-09 4
242 2003-01-01 2002-01-11 5
243 2003-01-01 2002-01-13 6
244 2002-01-14 2002-01-15 7
245 2002-01-16 2002-01-17 8
246 2002-01-18 2002-01-19 9
247 2002-01-20 2002-01-21 10
248 UPDATE t1 SET c1='2003-01-01' WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC LIMIT 2;
249 SELECT * FROM t1 WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC;
250 c1 c2 c3
251 2003-01-01 2002-01-23 11
252 UPDATE t1 SET c1='2003-01-01' WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2 LIMIT 2;
253 SELECT * FROM t1 WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2;
254 c1 c2 c3
255 2003-01-01 2002-01-25 12
256 2003-01-01 2002-01-27 13
257 2002-01-28 2002-01-29 14
258 UPDATE t1 SET c1='2003-01-01' WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC LIMIT 2;
259 SELECT * FROM t1 WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC;
260 c1 c2 c3
261 2003-01-01 2002-01-31 15
262 2003-01-01 2002-01-29 14
263 UPDATE t1 SET c1='2003-01-01' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
264 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
265 c1 c2 c3
266 UPDATE t1 SET c1='2003-01-01' WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2 LIMIT 2;
267 SELECT * FROM t1 WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2;
268 c1 c2 c3
269 INSERT INTO t1 VALUES('2002-03-01','2002-03-01',1),('2002-03-03','2002-03-04',2),('2002-03-05','2002-03-06',3),('2005-01-01','2005-01-02',4),('2005-01-03','2005-01-04',5),('2005-01-05','2005-01-06',6),('2005-01-07','2005-01-08',7),('2005-01-09',NULL,8);
270 SELECT * FROM t1;
271 c1 c2 c3
272 2002-01-14 2002-01-15 7
273 2002-01-16 2002-01-17 8
274 2002-01-18 2002-01-19 9
275 2002-01-20 2002-01-21 10
276 2002-02-01 2002-02-02 16
277 2002-02-03 2002-02-04 17
278 2002-03-01 2002-03-01 1
279 2002-03-03 2002-03-04 2
280 2002-03-05 2002-03-06 3
281 2003-01-01 2002-01-09 4
282 2003-01-01 2002-01-11 5
283 2003-01-01 2002-01-13 6
284 2003-01-01 2002-01-23 11
285 2003-01-01 2002-01-25 12
286 2003-01-01 2002-01-27 13
287 2003-01-01 2002-01-29 14
288 2003-01-01 2002-01-31 15
289 2003-01-01 2002-02-06 18
290 2003-01-01 2002-02-08 19
291 2003-01-01 2002-02-10 20
292 2005-01-01 2005-01-02 4
293 2005-01-03 2005-01-04 5
294 2005-01-05 2005-01-06 6
295 2005-01-07 2005-01-08 7
296 2005-01-09 NULL 8
297 SELECT c1 FROM t1;
298 c1
299 2002-01-14
300 2002-01-16
301 2002-01-18
302 2002-01-20
303 2002-02-01
304 2002-02-03
305 2002-03-01
306 2002-03-03
307 2002-03-05
308 2003-01-01
309 2003-01-01
310 2003-01-01
311 2003-01-01
312 2003-01-01
313 2003-01-01
314 2003-01-01
315 2003-01-01
316 2003-01-01
317 2003-01-01
318 2003-01-01
319 2005-01-01
320 2005-01-03
321 2005-01-05
322 2005-01-07
323 2005-01-09
324 SELECT c1 FROM t1 ORDER BY c1 DESC;
325 c1
326 2005-01-09
327 2005-01-07
328 2005-01-05
329 2005-01-03
330 2005-01-01
331 2003-01-01
332 2003-01-01
333 2003-01-01
334 2003-01-01
335 2003-01-01
336 2003-01-01
337 2003-01-01
338 2003-01-01
339 2003-01-01
340 2003-01-01
341 2003-01-01
342 2002-03-05
343 2002-03-03
344 2002-03-01
345 2002-02-03
346 2002-02-01
347 2002-01-20
348 2002-01-18
349 2002-01-16
350 2002-01-14
351 SELECT * FROM t1 WHERE c1='2003-01-01';
352 c1 c2 c3
353 2003-01-01 2002-01-09 4
354 2003-01-01 2002-01-11 5
355 2003-01-01 2002-01-13 6
356 2003-01-01 2002-01-23 11
357 2003-01-01 2002-01-25 12
358 2003-01-01 2002-01-27 13
359 2003-01-01 2002-01-29 14
360 2003-01-01 2002-01-31 15
361 2003-01-01 2002-02-06 18
362 2003-01-01 2002-02-08 19
363 2003-01-01 2002-02-10 20
364 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 LIMIT 2;
365 c1 c2 c3
366 2002-01-14 2002-01-15 7
367 2002-01-16 2002-01-17 8
368 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 DESC;
369 c1 c2 c3
370 2002-01-14 2002-01-15 7
371 2002-01-16 2002-01-17 8
372 2002-01-18 2002-01-19 9
373 2002-01-20 2002-01-21 10
374 2002-02-01 2002-02-02 16
375 2002-02-03 2002-02-04 17
376 2002-03-01 2002-03-01 1
377 2002-03-03 2002-03-04 2
378 2002-03-05 2002-03-06 3
379 2005-01-01 2005-01-02 4
380 2005-01-03 2005-01-04 5
381 2005-01-05 2005-01-06 6
382 2005-01-07 2005-01-08 7
383 2005-01-09 NULL 8
384 SELECT * FROM t1 WHERE c1 > '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
385 c1 c2 c3
386 2005-01-01 2005-01-02 4
387 2005-01-03 2005-01-04 5
388 SELECT * FROM t1 WHERE c1 >= '2003-01-01' ORDER BY c1,c2;
389 c1 c2 c3
390 2003-01-01 2002-01-09 4
391 2003-01-01 2002-01-11 5
392 2003-01-01 2002-01-13 6
393 2003-01-01 2002-01-23 11
394 2003-01-01 2002-01-25 12
395 2003-01-01 2002-01-27 13
396 2003-01-01 2002-01-29 14
397 2003-01-01 2002-01-31 15
398 2003-01-01 2002-02-06 18
399 2003-01-01 2002-02-08 19
400 2003-01-01 2002-02-10 20
401 2005-01-01 2005-01-02 4
402 2005-01-03 2005-01-04 5
403 2005-01-05 2005-01-06 6
404 2005-01-07 2005-01-08 7
405 2005-01-09 NULL 8
406 SELECT * FROM t1 WHERE c1 < '2003-01-01' ORDER BY c1,c2 LIMIT 2;
407 c1 c2 c3
408 2002-01-14 2002-01-15 7
409 2002-01-16 2002-01-17 8
410 SELECT * FROM t1 WHERE c1 <= '2003-01-01' ORDER BY c1,c2 DESC;
411 c1 c2 c3
412 2002-01-14 2002-01-15 7
413 2002-01-16 2002-01-17 8
414 2002-01-18 2002-01-19 9
415 2002-01-20 2002-01-21 10
416 2002-02-01 2002-02-02 16
417 2002-02-03 2002-02-04 17
418 2002-03-01 2002-03-01 1
419 2002-03-03 2002-03-04 2
420 2002-03-05 2002-03-06 3
421 2003-01-01 2002-02-10 20
422 2003-01-01 2002-02-08 19
423 2003-01-01 2002-02-06 18
424 2003-01-01 2002-01-31 15
425 2003-01-01 2002-01-29 14
426 2003-01-01 2002-01-27 13
427 2003-01-01 2002-01-25 12
428 2003-01-01 2002-01-23 11
429 2003-01-01 2002-01-13 6
430 2003-01-01 2002-01-11 5
431 2003-01-01 2002-01-09 4
432 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
433 c1 c2 c3
434 2003-01-01 2002-02-10 20
435 2003-01-01 2002-02-08 19
436 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2;
437 c1 c2 c3
438 2003-01-01 2002-01-09 4
439 2003-01-01 2002-01-11 5
440 2003-01-01 2002-01-13 6
441 2003-01-01 2002-01-23 11
442 2003-01-01 2002-01-25 12
443 2003-01-01 2002-01-27 13
444 2003-01-01 2002-01-29 14
445 2003-01-01 2002-01-31 15
446 2003-01-01 2002-02-06 18
447 2003-01-01 2002-02-08 19
448 2003-01-01 2002-02-10 20
449 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 LIMIT 2;
450 c1 c2 c3
451 2002-03-01 2002-03-01 1
452 2002-03-03 2002-03-04 2
453 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 DESC;
454 c1 c2 c3
455 2002-03-01 2002-03-01 1
456 2002-03-03 2002-03-04 2
457 2002-03-05 2002-03-06 3
458 2003-01-01 2002-02-10 20
459 2003-01-01 2002-02-08 19
460 2003-01-01 2002-02-06 18
461 2003-01-01 2002-01-31 15
462 2003-01-01 2002-01-29 14
463 2003-01-01 2002-01-27 13
464 2003-01-01 2002-01-25 12
465 2003-01-01 2002-01-23 11
466 2003-01-01 2002-01-13 6
467 2003-01-01 2002-01-11 5
468 2003-01-01 2002-01-09 4
469 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2 DESC LIMIT 2;
470 c1 c2 c3
471 2003-01-01 2002-02-10 20
472 2003-01-01 2002-02-08 19
473 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2;
474 c1 c2 c3
475 2003-01-01 2002-01-09 4
476 2003-01-01 2002-01-11 5
477 2003-01-01 2002-01-13 6
478 2003-01-01 2002-01-23 11
479 2003-01-01 2002-01-25 12
480 2003-01-01 2002-01-27 13
481 2003-01-01 2002-01-29 14
482 2003-01-01 2002-01-31 15
483 2003-01-01 2002-02-06 18
484 2003-01-01 2002-02-08 19
485 2003-01-01 2002-02-10 20
486 2005-01-07 2005-01-08 7
487 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
488 c1 c2 c3
489 2005-01-09 NULL 8
490 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
491 c1 c2 c3
492 2005-01-09 NULL 8
493 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
494 c1 c2 c3
495 2003-01-01 2002-01-09 4
496 2003-01-01 2002-01-11 5
497 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
498 c1 c2 c3
499 2003-01-01 2002-01-09 4
500 2003-01-01 2002-01-11 5
501 2003-01-01 2002-01-13 6
502 2002-01-14 2002-01-15 7
503 2002-01-16 2002-01-17 8
504 2002-01-18 2002-01-19 9
505 2002-01-20 2002-01-21 10
506 2003-01-01 2002-01-23 11
507 2003-01-01 2002-01-25 12
508 2003-01-01 2002-01-27 13
509 2003-01-01 2002-01-29 14
510 2003-01-01 2002-01-31 15
511 2002-02-01 2002-02-02 16
512 2002-02-03 2002-02-04 17
513 2003-01-01 2002-02-06 18
514 2003-01-01 2002-02-08 19
515 2003-01-01 2002-02-10 20
516 2002-03-01 2002-03-01 1
517 2002-03-03 2002-03-04 2
518 2002-03-05 2002-03-06 3
519 2005-01-01 2005-01-02 4
520 2005-01-03 2005-01-04 5
521 2005-01-05 2005-01-06 6
522 2005-01-07 2005-01-08 7
523 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1 DESC LIMIT 2;
524 c1 c2 c3
525 2003-01-01 2002-01-09 4
526 2003-01-01 2002-01-11 5
527 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1;
528 c1 c2 c3
529 2003-01-01 2002-01-09 4
530 2003-01-01 2002-01-11 5
531 2003-01-01 2002-01-13 6
532 2003-01-01 2002-01-23 11
533 2003-01-01 2002-01-25 12
534 2003-01-01 2002-01-27 13
535 2003-01-01 2002-01-29 14
536 2003-01-01 2002-01-31 15
537 2003-01-01 2002-02-06 18
538 2003-01-01 2002-02-08 19
539 2003-01-01 2002-02-10 20
540 2005-01-01 2005-01-02 4
541 2005-01-03 2005-01-04 5
542 2005-01-05 2005-01-06 6
543 2005-01-07 2005-01-08 7
544 SELECT * FROM t1 WHERE c1='2003-01-03' OR c2='2002-03-06';
545 c1 c2 c3
546 2002-03-05 2002-03-06 3
547 UPDATE t1 SET c1='2006-01-01' WHERE c1='2003-01-03' OR c2='2002-03-06';
548 SELECT * FROM t1 WHERE c1='2006-01-01' OR c2='2002-03-04';
549 c1 c2 c3
550 2002-03-03 2002-03-04 2
551 2006-01-01 2002-03-06 3
552 TRUNCATE TABLE t1;
553 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD PRIMARY KEY(c1);
554 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
555 Warnings:
556 Note 1265 Data truncated for column 'c1' at row 1
557 Note 1265 Data truncated for column 'c2' at row 1
558 Note 1265 Data truncated for column 'c1' at row 2
559 Note 1265 Data truncated for column 'c2' at row 2
560 Note 1265 Data truncated for column 'c1' at row 3
561 Note 1265 Data truncated for column 'c2' at row 3
562 Note 1265 Data truncated for column 'c1' at row 4
563 Note 1265 Data truncated for column 'c2' at row 4
564 INSERT INTO t1 VALUES('1000-01-01','1000-01-01',1),('9999-12-31','9999-12-31',2);
565 SELECT * FROM t1 WHERE c1 <='1000-01-01';
566 c1 c2 c3
567 1000-01-01 1000-01-01 1
568 DELETE FROM t1 WHERE c1 <='1000-01-01';
569 SELECT * FROM t1 WHERE c1 <='1000-01-01';
570 c1 c2 c3
571 SELECT * FROM t1 WHERE c1 >='9999-12-31';
572 c1 c2 c3
573 9999-12-31 9999-12-31 2
574 DELETE FROM t1 WHERE c1 >='9999-12-31';
575 SELECT * FROM t1 WHERE c1 >='9999-12-31';
576 c1 c2 c3
577 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
578 c1 c2 c3
579 2009-02-28 2009-02-28 6
580 2009-02-27 2009-02-27 5
581 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
582 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
583 c1 c2 c3
584 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
585 c1 c2 c3
586 2009-02-26 2009-02-26 4
587 DELETE FROM t1 WHERE c1 <> CURDATE() ORDER BY c1 LIMIT 2;
588 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
589 c1 c2 c3
590 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
591 Warnings:
592 Note 1265 Data truncated for column 'c2' at row 1
593 Note 1265 Data truncated for column 'c1' at row 2
594 Note 1265 Data truncated for column 'c2' at row 2
595 Note 1265 Data truncated for column 'c1' at row 3
596 Note 1265 Data truncated for column 'c2' at row 3
597 Note 1265 Data truncated for column 'c1' at row 4
598 Note 1265 Data truncated for column 'c2' at row 4
599 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
600 c1 c2 c3
601 2001-01-01 2009-02-25 7
602 2009-02-25 2009-02-25 3
603 2009-02-26 2009-02-26 8
604 2009-02-27 2009-02-27 9
605 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
606 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
607 c1 c2 c3
608 2009-02-26 2009-02-26 8
609 2009-02-27 2009-02-27 9
610 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
611 c1 c2 c3
612 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
613 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
614 c1 c2 c3
615 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
616 c1 c2 c3
617 2009-02-26 2009-02-26 8
618 Warnings:
619 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
620 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
621 DELETE FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
622 Warnings:
623 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
624 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
625 c1 c2 c3
626 Warnings:
627 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
628 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
629 Warnings:
630 Note 1265 Data truncated for column 'c2' at row 1
631 Note 1265 Data truncated for column 'c1' at row 2
632 Note 1265 Data truncated for column 'c2' at row 2
633 Note 1265 Data truncated for column 'c1' at row 3
634 Note 1265 Data truncated for column 'c1' at row 4
635 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
636 c1 c2 c3
637 2009-02-26 2009-02-26 12
638 2009-02-27 2009-02-27 9
639 DELETE FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
640 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
641 c1 c2 c3
642 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
643 c1 c2 c3
644 2009-02-28 2009-02-28 10
645 Warnings:
646 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
647 DELETE FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
648 Warnings:
649 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
650 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
651 c1 c2 c3
652 Warnings:
653 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
654 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
655 c1 c2 c3
656 2009-03-01 NULL 13
657 2009-03-02 NULL 14
658 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
659 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
660 c1 c2 c3
661 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
662 Warnings:
663 Note 1265 Data truncated for column 'c1' at row 1
664 Note 1265 Data truncated for column 'c2' at row 1
665 Note 1265 Data truncated for column 'c1' at row 2
666 Note 1265 Data truncated for column 'c1' at row 3
667 Note 1265 Data truncated for column 'c2' at row 3
668 Note 1265 Data truncated for column 'c1' at row 4
669 Note 1265 Data truncated for column 'c2' at row 4
670 Note 1265 Data truncated for column 'c1' at row 5
671 Note 1265 Data truncated for column 'c2' at row 5
672 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
673 c1 c2 c3
674 2001-01-01 2009-02-25 11
675 2009-03-03 2009-03-03 15
676 2009-03-05 2009-03-05 17
677 2009-03-06 2009-03-06 18
678 2009-03-07 2009-03-07 19
679 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
680 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
681 c1 c2 c3
682 2009-03-05 2009-03-05 17
683 2009-03-06 2009-03-06 18
684 2009-03-07 2009-03-07 19
685 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
686 c1 c2 c3
687 2009-03-05 2009-03-05 17
688 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1 LIMIT 2;
689 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
690 c1 c2 c3
691 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
692 c1 c2 c3
693 2009-03-06 2009-03-06 18
694 2009-03-07 2009-03-07 19
695 Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
696 Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
697 Warnings:
698 DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
699 Warnings:
700 Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
701 Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
702 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
703 c1 c2 c3
704 SELECT count(*) FROM t1;
705 count(*)
706 1
707 TRUNCATE TABLE t1;
708 SELECT count(*) FROM t1;
709 count(*)
710 0
711 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
712 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
713 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
714 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
715 c1 c2 c3 c1 c2 c3
716 2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
717 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c1;
718 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
719 c1 c2 c3 c1 c2 c3
720 TRUNCATE TABLE t1;
721 TRUNCATE TABLE t2;
722 INSERT INTO t1 VALUES('2001-01-01','2001-01-02',1),('2001-01-03','2001-01-02',2);
723 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
724 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
725 c1 c2 c3 c1 c2 c3
726 2001-01-01 2001-01-02 1 2001-01-02 2001-01-03 1
727 2001-01-03 2001-01-02 2 2001-01-02 2001-01-03 1
728 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
729 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
730 c1 c2 c3 c1 c2 c3
731 DROP TABLE t1,t2;
732 CREATE TABLE t1(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME NULL, c3 INT, INDEX idx2(c2));
733 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
734 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
735 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
736 c1 hour(c1) minute(c1) second(c1)
737 2001-01-01 00:00:00 0 0 0
738 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
739 UPDATE t1 SET c1='2009-01-01 1:2:3',c2='2009-01-02 23:59:59.999999' WHERE c2='2009-01-02 23:59:59';
740 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
741 c1 ADDTIME(c2,'1 1:1:1.000002')
742 2009-01-01 01:02:03 2009-01-04 01:01:00.000002
743 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
744 SET TIMESTAMP=1235553613;
745 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
746 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
747 SELECT * FROM t1;
748 c1 c2 c3
749 2001-01-01 00:00:00 2010-01-01 14:12:33 1
750 2009-01-01 01:02:03 2009-01-02 23:59:59 2
751 2009-02-26 01:01:01 2009-02-25 12:20:13 3
752 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
753 SELECT * FROM t1 WHERE c2 IS NULL;
754 c1 c2 c3
755 2001-01-08 00:00:00 NULL 5
756 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06 00:00:00';
757 SELECT * FROM t1 WHERE c2 IS NULL;
758 c1 c2 c3
759 2001-01-06 00:00:00 NULL 4
760 2001-01-08 00:00:00 NULL 5
761 UPDATE t1 SET c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
762 SELECT * FROM t1 WHERE c2='2001-01-10';
763 c1 c2 c3
764 2001-01-06 00:00:00 2001-01-10 00:00:00 4
765 2001-01-08 00:00:00 2001-01-10 00:00:00 5
766 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
767 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
768 Warnings:
769 Warning 1265 Data truncated for column 'c1' at row 1
770 Warning 1265 Data truncated for column 'c2' at row 1
771 UPDATE t1 SET c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
772 Warnings:
773 Warning 1265 Data truncated for column 'c2' at row 1
774 SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
775 count(*)
776 1
777 Warnings:
778 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59'
779 Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59'
780 SELECT count(*) FROM t1 WHERE c2='2001-01-11 23:59:60' /* returns 0 */;
781 count(*)
782 0
783 Warnings:
784 Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
785 Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
786 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
787 c1 c2 c3
788 0000-00-00 00:00:00 0000-00-00 00:00:00 6
789 2001-01-11 00:00:00 0000-00-00 00:00:00 7
790 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
791 SET SQL_MODE=TRADITIONAL;
792 SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
793 c1 c2 c3
794 0000-00-00 00:00:00 0000-00-00 00:00:00 6
795 2001-01-11 00:00:00 0000-00-00 00:00:00 7
796 2001-01-17 00:00:00 0000-00-00 00:00:00 8
797 Warnings:
798 Warning 1264 Out of range value for column 'c2' at row 1
799 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
800 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
801 SET SQL_MODE=DEFAULT;
802 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
803 UPDATE t1 SET c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
804 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
805 c1 c2 c3
806 0000-00-00 00:00:00 0000-00-00 00:00:00 6
807 2001-01-11 00:00:00 0000-00-00 00:00:00 7
808 2001-01-18 00:00:00 0000-00-00 00:00:00 9
809 SELECT * FROM t1 WHERE c2='2001-01-17 12:34:00';
810 c1 c2 c3
811 2001-01-17 00:00:00 2001-01-17 12:34:00 8
812 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
813 UPDATE t1 SET c1='1000-01-01 00:00:00',c2='9999-12-31 23:59:59' WHERE c1='2001-01-21 05:43:43';
814 SELECT * FROM t1 WHERE c1='1000-01-01 00:00:00' AND c2='9999-12-31 23:59:59';
815 c1 c2 c3
816 1000-01-01 00:00:00 9999-12-31 23:59:59 10
817 INSERT INTO t1 VALUES('2001-01-23 02:33','2001-01-24 03:33',11),('2001-01-25 04:33','2001-01-26 05:33',12);
818 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
819 ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
820 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
821 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
822 count(*)
823 1
824 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to 0000-00-00 00:00:00 */;
825 ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'
826 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24 03:33';
827 Warnings:
828 Warning 1048 Column 'c1' cannot be null
829 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00';
830 c1 c2 c3
831 0000-00-00 00:00:00 0000-00-00 00:00:00 6
832 TRUNCATE TABLE t1;
833 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
834 INSERT INTO t1 VALUES('2001-01-02 2:30:45','2001-01-01 1:30',1),('2001-01-03 3:30','2001-01-02 2:30:45',2);
835 INSERT INTO t2 VALUES('2001-01-02 2:30:45','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
836 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
837 c1 c2 c3 c1 c2 c3
838 2001-01-02 02:30:45 2001-01-01 01:30:00 1 2001-01-02 02:30:45 2001-01-03 03:30:00 1
839 UPDATE t1,t2 SET t2.c1='2005-01-06 2:30:50',t1.c1='2005-01-06 2:30:50' WHERE t2.c1=t1.c1;
840 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
841 c1 c2 c3 c1 c2 c3
842 2005-01-06 02:30:50 2001-01-01 01:30:00 1 2005-01-06 02:30:50 2001-01-03 03:30:00 1
843 INSERT INTO t1 VALUES('2002-01-01 3:30','2002-01-02 2:30:45',3),('2002-01-04 4:30','2002-01-02 2:30:45',4);
844 INSERT INTO t2 VALUES('2002-01-02 2:30:45','2002-01-03 3:30',3),('2002-01-03 3:30','2002-01-04 4:30',4);
845 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
846 c1 c2 c3 c1 c2 c3
847 2002-01-01 03:30:00 2002-01-02 02:30:45 3 2002-01-02 02:30:45 2002-01-03 03:30:00 3
848 2002-01-04 04:30:00 2002-01-02 02:30:45 4 2002-01-02 02:30:45 2002-01-03 03:30:00 3
849 UPDATE t1,t2 SET t2.c1='2002-01-06 2:30:50',t1.c2='2002-01-06 2:30:50' WHERE t2.c1=t1.c2;
850 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
851 c1 c2 c3 c1 c2 c3
852 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
853 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
854 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
855 c1 c2 c3 c1 c2 c3
856 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
857 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
858 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
859 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
860 c1 c2 c3 c1 c2 c3
861 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
862 DROP TABLE t2;
863 TRUNCATE TABLE t1;
864 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, DROP PRIMARY KEY;
865 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD KEY(c1);
866 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
867 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
868 c1 c2 c3
869 INSERT INTO t1 VALUES('2002-01-08 1:30','2002-01-09 1:30',4),('2002-01-08 1:30:5','2002-01-09 1:30:5',5),('2002-01-08 1:30:10','2002-01-09 1:30:10',6),('2002-01-08 1:30:15','2002-01-09 1:30:15',7),('2002-01-08 1:30:20','2002-01-09 1:30:20',8),('2002-01-08 1:30:25','2002-01-09 1:30:25',9),('2002-01-08 1:30:30','2002-01-09 1:30:30',10),('2002-01-08 1:30:35','2002-01-09 1:30:35',11),('2002-01-08 1:30:40','2002-01-09 1:30:40',12),('2002-01-08 1:30:45','2002-01-09 1:30:45',13),('2002-01-08 1:30:50','2002-01-09 1:30:50',14),('2002-01-08 1:30:55','2002-01-09 1:30:55',15),('2002-01-08 1:30:59','2002-01-09 1:30:59',16),('2002-01-08 2:00','2002-01-09 2:00',17),('2002-01-08 2:00:05','2002-01-09 2:00:05',18),('2002-01-08 2:00:10','2002-01-09 2:00:10',19),('2002-01-08 2:00:15','2002-01-09 2:00:15',20);
870 SELECT * FROM t1;
871 c1 c2 c3
872 2002-01-08 01:30:00 2002-01-09 01:30:00 4
873 2002-01-08 01:30:05 2002-01-09 01:30:05 5
874 2002-01-08 01:30:10 2002-01-09 01:30:10 6
875 2002-01-08 01:30:15 2002-01-09 01:30:15 7
876 2002-01-08 01:30:20 2002-01-09 01:30:20 8
877 2002-01-08 01:30:25 2002-01-09 01:30:25 9
878 2002-01-08 01:30:30 2002-01-09 01:30:30 10
879 2002-01-08 01:30:35 2002-01-09 01:30:35 11
880 2002-01-08 01:30:40 2002-01-09 01:30:40 12
881 2002-01-08 01:30:45 2002-01-09 01:30:45 13
882 2002-01-08 01:30:50 2002-01-09 01:30:50 14
883 2002-01-08 01:30:55 2002-01-09 01:30:55 15
884 2002-01-08 01:30:59 2002-01-09 01:30:59 16
885 2002-01-08 02:00:00 2002-01-09 02:00:00 17
886 2002-01-08 02:00:05 2002-01-09 02:00:05 18
887 2002-01-08 02:00:10 2002-01-09 02:00:10 19
888 2002-01-08 02:00:15 2002-01-09 02:00:15 20
889 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
890 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
891 c1 c2 c3
892 2003-01-01 01:02:03 2002-01-09 01:30:05 5
893 2003-01-01 01:02:03 2002-01-09 01:30:10 6
894 2002-01-08 01:30:15 2002-01-09 01:30:15 7
895 2002-01-08 01:30:20 2002-01-09 01:30:20 8
896 2002-01-08 01:30:25 2002-01-09 01:30:25 9
897 2002-01-08 01:30:30 2002-01-09 01:30:30 10
898 2002-01-08 01:30:35 2002-01-09 01:30:35 11
899 2002-01-08 01:30:40 2002-01-09 01:30:40 12
900 2002-01-08 01:30:45 2002-01-09 01:30:45 13
901 2002-01-08 01:30:50 2002-01-09 01:30:50 14
902 2002-01-08 01:30:55 2002-01-09 01:30:55 15
903 2002-01-08 01:30:59 2002-01-09 01:30:59 16
904 2002-01-08 02:00:00 2002-01-09 02:00:00 17
905 2002-01-08 02:00:05 2002-01-09 02:00:05 18
906 2002-01-08 02:00:10 2002-01-09 02:00:10 19
907 2002-01-08 02:00:15 2002-01-09 02:00:15 20
908 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
909 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
910 c1 c2 c3
911 2003-01-01 01:02:03 2002-01-09 02:00:15 20
912 2003-01-01 01:02:03 2002-01-09 02:00:10 19
913 2002-01-08 02:00:05 2002-01-09 02:00:05 18
914 2002-01-08 02:00:00 2002-01-09 02:00:00 17
915 2002-01-08 01:30:59 2002-01-09 01:30:59 16
916 2002-01-08 01:30:55 2002-01-09 01:30:55 15
917 2002-01-08 01:30:50 2002-01-09 01:30:50 14
918 2002-01-08 01:30:45 2002-01-09 01:30:45 13
919 2002-01-08 01:30:40 2002-01-09 01:30:40 12
920 2002-01-08 01:30:35 2002-01-09 01:30:35 11
921 2002-01-08 01:30:30 2002-01-09 01:30:30 10
922 2002-01-08 01:30:25 2002-01-09 01:30:25 9
923 2002-01-08 01:30:20 2002-01-09 01:30:20 8
924 2002-01-08 01:30:15 2002-01-09 01:30:15 7
925 2003-01-01 01:02:03 2002-01-09 01:30:10 6
926 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
927 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
928 c1 c2 c3
929 2003-01-01 01:02:03 2002-01-09 01:30:00 4
930 2003-01-01 01:02:03 2002-01-09 01:30:05 5
931 2003-01-01 01:02:03 2002-01-09 01:30:10 6
932 2002-01-08 01:30:15 2002-01-09 01:30:15 7
933 2002-01-08 01:30:20 2002-01-09 01:30:20 8
934 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
935 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
936 c1 c2 c3
937 2003-01-01 01:02:03 2002-01-09 01:30:30 10
938 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2 LIMIT 2;
939 Warnings:
940 Warning 1292 Incorrect datetime value: '20020109 01:30:40'
941 Warning 1292 Incorrect datetime value: '20020109 01:30:50'
942 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
943 c1 c2 c3
944 Warnings:
945 Warning 1292 Incorrect datetime value: '20020109 01:30:40'
946 Warning 1292 Incorrect datetime value: '20020109 01:30:50'
947 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC LIMIT 2;
948 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
949 c1 c2 c3
950 2003-01-01 01:02:03 2002-01-09 02:00:00 17
951 2003-01-01 01:02:03 2002-01-09 01:30:55 15
952 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
953 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
954 c1 c2 c3
955 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2 LIMIT 2;
956 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
957 c1 c2 c3
958 INSERT INTO t1 VALUES('20020301000001','20020302000001',1),('020303010030','020304010030',2),('050103000001','050104000001',3),('5-10-10 1:2:3','5-10-10 1:2:3',4),('0000-00-00 00:00:01',NULL,5);
959 SELECT * FROM t1;
960 c1 c2 c3
961 0000-00-00 00:00:01 NULL 5
962 0005-10-10 01:02:03 0005-10-10 01:02:03 4
963 2002-01-08 01:30:15 2002-01-09 01:30:15 7
964 2002-01-08 01:30:20 2002-01-09 01:30:20 8
965 2002-01-08 01:30:25 2002-01-09 01:30:25 9
966 2002-01-08 01:30:35 2002-01-09 01:30:35 11
967 2002-01-08 01:30:40 2002-01-09 01:30:40 12
968 2002-01-08 01:30:45 2002-01-09 01:30:45 13
969 2002-01-08 01:30:50 2002-01-09 01:30:50 14
970 2002-01-08 01:30:59 2002-01-09 01:30:59 16
971 2002-01-08 02:00:05 2002-01-09 02:00:05 18
972 2002-03-01 00:00:01 2002-03-02 00:00:01 1
973 2002-03-03 01:00:30 2002-03-04 01:00:30 2
974 2003-01-01 01:02:03 2002-01-09 01:30:00 4
975 2003-01-01 01:02:03 2002-01-09 01:30:05 5
976 2003-01-01 01:02:03 2002-01-09 01:30:10 6
977 2003-01-01 01:02:03 2002-01-09 01:30:30 10
978 2003-01-01 01:02:03 2002-01-09 01:30:55 15
979 2003-01-01 01:02:03 2002-01-09 02:00:00 17
980 2003-01-01 01:02:03 2002-01-09 02:00:10 19
981 2003-01-01 01:02:03 2002-01-09 02:00:15 20
982 2005-01-03 00:00:01 2005-01-04 00:00:01 3
983 SELECT c1 FROM t1;
984 c1
985 0000-00-00 00:00:01
986 0005-10-10 01:02:03
987 2002-01-08 01:30:15
988 2002-01-08 01:30:20
989 2002-01-08 01:30:25
990 2002-01-08 01:30:35
991 2002-01-08 01:30:40
992 2002-01-08 01:30:45
993 2002-01-08 01:30:50
994 2002-01-08 01:30:59
995 2002-01-08 02:00:05
996 2002-03-01 00:00:01
997 2002-03-03 01:00:30
998 2003-01-01 01:02:03
999 2003-01-01 01:02:03
1000 2003-01-01 01:02:03
1001 2003-01-01 01:02:03
1002 2003-01-01 01:02:03
1003 2003-01-01 01:02:03
1004 2003-01-01 01:02:03
1005 2003-01-01 01:02:03
1006 2005-01-03 00:00:01
1007 SELECT c1 FROM t1 ORDER BY c1 DESC;
1008 c1
1009 2005-01-03 00:00:01
1010 2003-01-01 01:02:03
1011 2003-01-01 01:02:03
1012 2003-01-01 01:02:03
1013 2003-01-01 01:02:03
1014 2003-01-01 01:02:03
1015 2003-01-01 01:02:03
1016 2003-01-01 01:02:03
1017 2003-01-01 01:02:03
1018 2002-03-03 01:00:30
1019 2002-03-01 00:00:01
1020 2002-01-08 02:00:05
1021 2002-01-08 01:30:59
1022 2002-01-08 01:30:50
1023 2002-01-08 01:30:45
1024 2002-01-08 01:30:40
1025 2002-01-08 01:30:35
1026 2002-01-08 01:30:25
1027 2002-01-08 01:30:20
1028 2002-01-08 01:30:15
1029 0005-10-10 01:02:03
1030 0000-00-00 00:00:01
1031 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1032 c1 c2 c3
1033 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1034 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1035 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1036 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1037 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1038 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1039 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1040 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1041 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1042 c1 c2 c3
1043 0000-00-00 00:00:01 NULL 5
1044 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1045 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1046 c1 c2 c3
1047 0000-00-00 00:00:01 NULL 5
1048 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1049 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1050 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1051 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1052 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1053 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1054 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1055 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1056 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1057 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1058 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1059 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1060 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1061 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1062 c1 c2 c3
1063 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1064 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1065 c1 c2 c3
1066 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1067 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1068 c1 c2 c3
1069 0000-00-00 00:00:01 NULL 5
1070 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1071 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1072 c1 c2 c3
1073 0000-00-00 00:00:01 NULL 5
1074 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1075 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1076 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1077 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1078 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1079 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1080 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1081 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1082 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1083 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1084 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1085 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1086 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1087 c1 c2 c3
1088 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1089 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1090 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1091 c1 c2 c3
1092 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1093 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1094 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1095 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1096 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1097 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1098 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1099 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1100 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1101 c1 c2 c3
1102 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1103 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1104 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1105 c1 c2 c3
1106 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1107 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1108 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1109 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1110 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1111 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1112 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1113 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1114 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1115 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1116 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2 DESC LIMIT 2;
1117 c1 c2 c3
1118 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1119 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1120 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1121 c1 c2 c3
1122 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1123 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1124 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1125 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1126 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1127 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1128 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1129 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1130 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1131 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1132 c1 c2 c3
1133 0000-00-00 00:00:01 NULL 5
1134 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1135 c1 c2 c3
1136 0000-00-00 00:00:01 NULL 5
1137 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1138 c1 c2 c3
1139 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1140 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1141 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1142 c1 c2 c3
1143 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1144 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1145 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1146 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1147 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1148 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1149 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1150 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1151 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1152 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1153 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1154 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1155 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1156 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1157 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1158 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1159 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1160 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1161 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1162 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1163 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1164 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1165 c1 c2 c3
1166 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1167 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1168 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1169 c1 c2 c3
1170 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1171 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1172 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1173 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1174 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1175 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1176 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1177 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1178 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1179 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1180 c1 c2 c3
1181 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1182 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1183 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1184 c1 c2 c3
1185 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1186 2006-01-01 00:00:01 2002-03-02 00:00:01 1
1187 TRUNCATE TABLE t1;
1188 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD PRIMARY KEY(c1);
1189 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
1190 INSERT INTO t1 VALUES('1000-01-01 00:00:00','1000-01-01 00:00:00',1),('9999-12-31 23:59:59','9999-12-31 23:59:59',2);
1191 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1192 c1 c2 c3
1193 1000-01-01 00:00:00 1000-01-01 00:00:00 1
1194 DELETE FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1195 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1196 c1 c2 c3
1197 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1198 c1 c2 c3
1199 9999-12-31 23:59:59 9999-12-31 23:59:59 2
1200 DELETE FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1201 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1202 c1 c2 c3
1203 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1204 c1 c2 c3
1205 2009-02-28 13:21:14 2009-02-28 13:21:14 6
1206 2009-02-27 13:21:14 2009-02-27 13:21:14 5
1207 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1208 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1209 c1 c2 c3
1210 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1211 c1 c2 c3
1212 2009-02-26 13:21:14 2009-02-26 13:21:14 4
1213 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1214 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1215 c1 c2 c3
1216 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
1217 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1218 c1 c2 c3
1219 2001-01-01 00:00:00 2009-02-25 12:20:13 7
1220 2009-02-25 12:20:13 2009-02-25 12:20:13 3
1221 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1222 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1223 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1224 c1 c2 c3
1225 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1226 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1227 c1 c2 c3
1228 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1229 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1230 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1231 c1 c2 c3
1232 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1233 c1 c2 c3
1234 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1235 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1236 c1 c2 c3
1237 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
1238 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1239 c1 c2 c3
1240 2009-02-26 13:21:14 2009-02-26 13:21:14 12
1241 2009-02-27 13:21:14 2009-02-27 13:21:14 9
1242 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1243 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1244 c1 c2 c3
1245 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1246 c1 c2 c3
1247 2009-02-28 13:21:14 2009-02-28 13:21:14 10
1248 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1249 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1250 c1 c2 c3
1251 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1252 c1 c2 c3
1253 2009-03-01 13:21:14 NULL 13
1254 2009-03-02 13:21:14 NULL 14
1255 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1256 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1257 c1 c2 c3
1258 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
1259 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1260 c1 c2 c3
1261 2001-01-01 00:00:00 2009-02-25 12:20:13 11
1262 2009-03-03 13:21:14 2009-03-03 13:21:14 15
1263 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1264 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1265 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1266 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1267 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1268 c1 c2 c3
1269 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1270 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1271 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1272 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1273 c1 c2 c3
1274 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1275 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1276 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1277 c1 c2 c3
1278 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1279 c1 c2 c3
1280 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1281 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1282 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1283 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1284 c1 c2 c3
1285 SELECT count(*) FROM t1;
1286 count(*)
1287 1
1288 TRUNCATE TABLE t1;
1289 SELECT count(*) FROM t1;
1290 count(*)
1291 0
1292 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
1293 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1294 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1295 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1296 c1 c2 c3 c1 c2 c3
1297 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1298 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1299 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1300 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1301 c1 c2 c3 c1 c2 c3
1302 TRUNCATE TABLE t1;
1303 TRUNCATE TABLE t2;
1304 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1305 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1306 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1307 c1 c2 c3 c1 c2 c3
1308 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1309 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1310 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1311 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1312 c1 c2 c3 c1 c2 c3
1313 DROP TABLE t1,t2;
1314 CREATE TABLE mt1(id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1315 INSERT INTO mt1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
1316 CREATE TABLE mt2 (id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1317 INSERT INTO mt2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
1318 CREATE TABLE mt3 (id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id));
1319 INSERT INTO mt3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 00:00:00'),(7,'2002-07-22 00:00:00');
1320 delete mt1,mt2,mt3 from mt1,mt2,mt3 where to_days(now())-to_days(mt3.mydate)>=30 and mt3.id=mt1.id and mt3.id=mt2.id;
1321 select * from mt3;
1322 id mydate
1323 1 2002-02-04 00:00:00
1324 5 2002-05-12 00:00:00
1325 6 2002-06-22 00:00:00
1326 7 2002-07-22 00:00:00
1327 DROP TABLE mt1,mt2,mt3;
1328 CREATE TABLE t1(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT, INDEX idx2(c2));
1329 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
1330 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
1331 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
1332 c1 hour(c1) minute(c1) second(c1)
1333 2001-01-01 00:00:00 0 0 0
1334 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
1335 UPDATE t1 SET c1='2009-01-01 1:2:3',c2='2009-01-02 23:59:59.999999' WHERE c2='2009-01-02 23:59:59';
1336 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
1337 c1 ADDTIME(c2,'1 1:1:1.000002')
1338 2009-01-01 01:02:03 2009-01-04 01:01:00.000002
1339 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
1340 SET TIMESTAMP=1235553613;
1341 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE() /* updates c1 with current timestamp */;
1342 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
1343 SELECT * FROM t1;
1344 c1 c2 c3
1345 2001-01-01 00:00:00 2010-01-01 14:12:33 1
1346 2009-01-01 01:02:03 2009-01-02 23:59:59 2
1347 2009-02-26 01:01:01 2009-02-25 12:20:13 3
1348 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
1349 UPDATE t1 SET c1='20010106010030',c2=NULL WHERE c1='2001-01-06 00:00:00';
1350 SELECT c1,c2 FROM t1 WHERE c2 IS NULL;
1351 c1 c2
1352 2001-01-06 01:00:30 NULL
1353 2001-01-08 00:00:00 NULL
1354 SELECT * FROM t1;
1355 c1 c2 c3
1356 2001-01-01 00:00:00 2010-01-01 14:12:33 1
1357 2001-01-06 01:00:30 NULL 4
1358 2001-01-08 00:00:00 NULL 5
1359 2009-01-01 01:02:03 2009-01-02 23:59:59 2
1360 2009-02-26 01:01:01 2009-02-25 12:20:13 3
1361 UPDATE t1 SET c1=c1,c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
1362 SELECT * FROM t1 WHERE c2='2001-01-10';
1363 c1 c2 c3
1364 2001-01-06 01:00:30 2001-01-10 00:00:00 4
1365 2001-01-08 00:00:00 2001-01-10 00:00:00 5
1366 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
1367 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
1368 Warnings:
1369 Warning 1265 Data truncated for column 'c1' at row 1
1370 Warning 1265 Data truncated for column 'c2' at row 1
1371 UPDATE t1 SET c1='2001-01-11 23:59:59',c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
1372 Warnings:
1373 Warning 1265 Data truncated for column 'c2' at row 1
1374 SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
1375 count(*)
1376 1
1377 Warnings:
1378 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59'
1379 Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59'
1380 SELECT count(*) FROM t1 WHERE c1='2001-01-11 23:59:59' AND c2='2001-01-11 23:59:60';
1381 count(*)
1382 1
1383 Warnings:
1384 Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60'
1385 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1386 c1 c2 c3
1387 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1388 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1389 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
1390 SET SQL_MODE=TRADITIONAL;
1391 SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
1392 c1 c2 c3
1393 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1394 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1395 2001-01-17 00:00:00 0000-00-00 00:00:00 8
1396 Warnings:
1397 Warning 1265 Data truncated for column 'c2' at row 1
1398 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1399 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1400 SET SQL_MODE=DEFAULT;
1401 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1402 UPDATE t1 SET c1='2001-01-17 12:34:00',c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
1403 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1404 c1 c2 c3
1405 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1406 1972-01-01 07:35:00 0000-00-00 00:00:00 9
1407 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1408 SELECT * FROM t1 WHERE c1='2001-01-17 12:34:00' AND c2='2001-01-17 12:34:00';
1409 c1 c2 c3
1410 2001-01-17 12:34:00 2001-01-17 12:34:00 8
1411 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
1412 UPDATE t1 SET c1='1971-01-01 00:00:01',c2='2038-01-09 03:14:07' WHERE c1='2001-01-21 05:43:43';
1413 SELECT * FROM t1 WHERE c1='1971-01-01 00:00:01' AND c2='2038-01-09 03:14:07';
1414 c1 c2 c3
1415 1971-01-01 00:00:01 2038-01-09 03:14:07 10
1416 INSERT INTO t1 VALUES('2001-01-23 02:33','2001-01-24 03:33',11),('2001-01-25 04:33','2001-01-26 05:33',12);
1417 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1418 ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
1419 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1420 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
1421 count(*)
1422 1
1423 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to current timestamp */;
1424 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-26 05:33';
1425 SELECT * FROM t1 WHERE c1=CURRENT_TIMESTAMP();
1426 c1 c2 c3
1427 2009-02-25 12:20:13 2001-01-24 03:33:00 11
1428 TRUNCATE TABLE t1;
1429 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1430 INSERT INTO t1 VALUES('2001-01-02 2:30:45','2001-01-01 1:30',1),('2001-01-03 3:30','2001-01-02 2:30:45',2);
1431 INSERT INTO t2 VALUES('2001-01-02 2:30:45','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1432 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1433 c1 c2 c3 c1 c2 c3
1434 2001-01-02 02:30:45 2001-01-01 01:30:00 1 2001-01-02 02:30:45 2001-01-03 03:30:00 1
1435 UPDATE t1,t2 SET t2.c1='2005-01-06 2:30:50',t1.c1='2005-01-06 2:30:50' WHERE t2.c1=t1.c1;
1436 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1437 c1 c2 c3 c1 c2 c3
1438 2005-01-06 02:30:50 2001-01-01 01:30:00 1 2005-01-06 02:30:50 2001-01-03 03:30:00 1
1439 INSERT INTO t1 VALUES('2002-01-01 3:30','2002-01-02 2:30:45',3),('2002-01-04 4:30','2002-01-02 2:30:45',4);
1440 INSERT INTO t2 VALUES('2002-01-02 2:30:45','2002-01-03 3:30',3),('2002-01-03 3:30','2002-01-04 4:30',4);
1441 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1442 c1 c2 c3 c1 c2 c3
1443 2002-01-01 03:30:00 2002-01-02 02:30:45 3 2002-01-02 02:30:45 2002-01-03 03:30:00 3
1444 2002-01-04 04:30:00 2002-01-02 02:30:45 4 2002-01-02 02:30:45 2002-01-03 03:30:00 3
1445 UPDATE t1,t2 SET t2.c1='2002-01-06 2:30:50',t1.c1=t1.c1,t1.c2='2002-01-06 2:30:50' WHERE t2.c1=t1.c2;
1446 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
1447 c1 c2 c3 c1 c2 c3
1448 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1449 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1450 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1451 c1 c2 c3 c1 c2 c3
1452 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1453 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1454 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
1455 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1456 c1 c2 c3 c1 c2 c3
1457 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1458 DROP TABLE t2;
1459 TRUNCATE TABLE t1;
1460 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, DROP PRIMARY KEY;
1461 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD KEY(c1);
1462 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
1463 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
1464 c1 c2 c3
1465 INSERT INTO t1 VALUES('2002-01-08 1:30','2002-01-09 1:30',4),('2002-01-08 1:30:5','2002-01-09 1:30:5',5),('2002-01-08 1:30:10','2002-01-09 1:30:10',6),('2002-01-08 1:30:15','2002-01-09 1:30:15',7),('2002-01-08 1:30:20','2002-01-09 1:30:20',8),('2002-01-08 1:30:25','2002-01-09 1:30:25',9),('2002-01-08 1:30:30','2002-01-09 1:30:30',10),('2002-01-08 1:30:35','2002-01-09 1:30:35',11),('2002-01-08 1:30:40','2002-01-09 1:30:40',12),('2002-01-08 1:30:45','2002-01-09 1:30:45',13),('2002-01-08 1:30:50','2002-01-09 1:30:50',14),('2002-01-08 1:30:55','2002-01-09 1:30:55',15),('2002-01-08 1:30:59','2002-01-09 1:30:59',16),('2002-01-08 2:00','2002-01-09 2:00',17),('2002-01-08 2:00:05','2002-01-09 2:00:05',18),('2002-01-08 2:00:10','2002-01-09 2:00:10',19),('2002-01-08 2:00:15','2002-01-09 2:00:15',20);
1466 SELECT * FROM t1;
1467 c1 c2 c3
1468 2002-01-08 01:30:00 2002-01-09 01:30:00 4
1469 2002-01-08 01:30:05 2002-01-09 01:30:05 5
1470 2002-01-08 01:30:10 2002-01-09 01:30:10 6
1471 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1472 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1473 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1474 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1475 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1476 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1477 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1478 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1479 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1480 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1481 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1482 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1483 2002-01-08 02:00:10 2002-01-09 02:00:10 19
1484 2002-01-08 02:00:15 2002-01-09 02:00:15 20
1485 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
1486 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
1487 c1 c2 c3
1488 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1489 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1490 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1491 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1492 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1493 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1494 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1495 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1496 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1497 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1498 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1499 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1500 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1501 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1502 2002-01-08 02:00:10 2002-01-09 02:00:10 19
1503 2002-01-08 02:00:15 2002-01-09 02:00:15 20
1504 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
1505 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
1506 c1 c2 c3
1507 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1508 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1509 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1510 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1511 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1512 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1513 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1514 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1515 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1516 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1517 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1518 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1519 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1520 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1521 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1522 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
1523 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
1524 c1 c2 c3
1525 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1526 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1527 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1528 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1529 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1530 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
1531 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
1532 c1 c2 c3
1533 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1534 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2 LIMIT 2;
1535 Warnings:
1536 Warning 1292 Incorrect datetime value: '20020109 01:30:40'
1537 Warning 1292 Incorrect datetime value: '20020109 01:30:50'
1538 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
1539 c1 c2 c3
1540 Warnings:
1541 Warning 1292 Incorrect datetime value: '20020109 01:30:40'
1542 Warning 1292 Incorrect datetime value: '20020109 01:30:50'
1543 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC LIMIT 2;
1544 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
1545 c1 c2 c3
1546 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1547 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1548 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1549 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
1550 c1 c2 c3
1551 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2 LIMIT 2;
1552 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
1553 c1 c2 c3
1554 INSERT INTO t1 VALUES('20020301000001','20020302000001',1),('020303010030','020304010030',2),('050103000001','050104000001',3),('5-10-10 1:2:3','5-10-10 1:2:3',4),('0000-00-00 00:00:01',NULL,5);
1555 Warnings:
1556 Warning 1264 Out of range value for column 'c1' at row 4
1557 Warning 1264 Out of range value for column 'c2' at row 4
1558 Warning 1265 Data truncated for column 'c1' at row 5
1559 SELECT * FROM t1;
1560 c1 c2 c3
1561 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1562 0000-00-00 00:00:00 NULL 5
1563 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1564 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1565 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1566 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1567 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1568 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1569 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1570 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1571 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1572 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1573 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1574 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1575 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1576 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1577 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1578 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1579 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1580 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1581 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1582 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1583 SELECT c1 FROM t1;
1584 c1
1585 0000-00-00 00:00:00
1586 0000-00-00 00:00:00
1587 2002-01-08 01:30:15
1588 2002-01-08 01:30:20
1589 2002-01-08 01:30:25
1590 2002-01-08 01:30:35
1591 2002-01-08 01:30:40
1592 2002-01-08 01:30:45
1593 2002-01-08 01:30:50
1594 2002-01-08 01:30:59
1595 2002-01-08 02:00:05
1596 2002-03-01 00:00:01
1597 2002-03-03 01:00:30
1598 2003-01-01 01:02:03
1599 2003-01-01 01:02:03
1600 2003-01-01 01:02:03
1601 2003-01-01 01:02:03
1602 2003-01-01 01:02:03
1603 2003-01-01 01:02:03
1604 2003-01-01 01:02:03
1605 2003-01-01 01:02:03
1606 2005-01-03 00:00:01
1607 SELECT c1 FROM t1 ORDER BY c1 DESC;
1608 c1
1609 2005-01-03 00:00:01
1610 2003-01-01 01:02:03
1611 2003-01-01 01:02:03
1612 2003-01-01 01:02:03
1613 2003-01-01 01:02:03
1614 2003-01-01 01:02:03
1615 2003-01-01 01:02:03
1616 2003-01-01 01:02:03
1617 2003-01-01 01:02:03
1618 2002-03-03 01:00:30
1619 2002-03-01 00:00:01
1620 2002-01-08 02:00:05
1621 2002-01-08 01:30:59
1622 2002-01-08 01:30:50
1623 2002-01-08 01:30:45
1624 2002-01-08 01:30:40
1625 2002-01-08 01:30:35
1626 2002-01-08 01:30:25
1627 2002-01-08 01:30:20
1628 2002-01-08 01:30:15
1629 0000-00-00 00:00:00
1630 0000-00-00 00:00:00
1631 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1632 c1 c2 c3
1633 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1634 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1635 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1636 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1637 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1638 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1639 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1640 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1641 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1642 c1 c2 c3
1643 0000-00-00 00:00:00 NULL 5
1644 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1645 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1646 c1 c2 c3
1647 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1648 0000-00-00 00:00:00 NULL 5
1649 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1650 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1651 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1652 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1653 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1654 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1655 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1656 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1657 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1658 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1659 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1660 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1661 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1662 c1 c2 c3
1663 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1664 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1665 c1 c2 c3
1666 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1667 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1668 c1 c2 c3
1669 0000-00-00 00:00:00 NULL 5
1670 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1671 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1672 c1 c2 c3
1673 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1674 0000-00-00 00:00:00 NULL 5
1675 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1676 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1677 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1678 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1679 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1680 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1681 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1682 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1683 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1684 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1685 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1686 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1687 c1 c2 c3
1688 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1689 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1690 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1691 c1 c2 c3
1692 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1693 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1694 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1695 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1696 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1697 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1698 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1699 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1700 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1701 c1 c2 c3
1702 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1703 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1704 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1705 c1 c2 c3
1706 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1707 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1708 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1709 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1710 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1711 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1712 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1713 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1714 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1715 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1716 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2 DESC LIMIT 2;
1717 c1 c2 c3
1718 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1719 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1720 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1721 c1 c2 c3
1722 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1723 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1724 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1725 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1726 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1727 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1728 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1729 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1730 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1731 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1732 c1 c2 c3
1733 0000-00-00 00:00:00 NULL 5
1734 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1735 c1 c2 c3
1736 0000-00-00 00:00:00 NULL 5
1737 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1738 c1 c2 c3
1739 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1740 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1741 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1742 c1 c2 c3
1743 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1744 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1745 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1746 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1747 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1748 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1749 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1750 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1751 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1752 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1753 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1754 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1755 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1756 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1757 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1758 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1759 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1760 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1761 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1762 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1763 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1764 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1765 c1 c2 c3
1766 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1767 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1768 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1769 c1 c2 c3
1770 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1771 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1772 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1773 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1774 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1775 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1776 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1777 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1778 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1779 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1780 c1 c2 c3
1781 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1782 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1783 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1784 c1 c2 c3
1785 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1786 2006-01-01 00:00:01 2002-03-02 00:00:01 1
1787 TRUNCATE TABLE t1;
1788 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD PRIMARY KEY(c1);
1789 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
1790 INSERT INTO t1 VALUES('1971-01-01 00:00:01','1971-01-01 00:00:01',1),('2038-01-09 03:14:07','2038-01-09 03:14:07',2);
1791 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1792 c1 c2 c3
1793 1971-01-01 00:00:01 1971-01-01 00:00:01 1
1794 DELETE FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1795 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1796 c1 c2 c3
1797 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1798 c1 c2 c3
1799 2038-01-09 03:14:07 2038-01-09 03:14:07 2
1800 DELETE FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1801 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1802 c1 c2 c3
1803 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1804 c1 c2 c3
1805 2009-02-28 13:21:14 2009-02-28 13:21:14 6
1806 2009-02-27 13:21:14 2009-02-27 13:21:14 5
1807 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1808 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1809 c1 c2 c3
1810 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1811 c1 c2 c3
1812 2009-02-26 13:21:14 2009-02-26 13:21:14 4
1813 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1814 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1815 c1 c2 c3
1816 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
1817 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1818 c1 c2 c3
1819 2001-01-01 00:00:00 2009-02-25 12:20:13 7
1820 2009-02-25 12:20:13 2009-02-25 12:20:13 3
1821 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1822 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1823 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1824 c1 c2 c3
1825 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1826 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1827 c1 c2 c3
1828 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1829 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1830 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1831 c1 c2 c3
1832 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1833 c1 c2 c3
1834 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1835 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1836 c1 c2 c3
1837 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
1838 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1839 c1 c2 c3
1840 2009-02-26 13:21:14 2009-02-26 13:21:14 12
1841 2009-02-27 13:21:14 2009-02-27 13:21:14 9
1842 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1843 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1844 c1 c2 c3
1845 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1846 c1 c2 c3
1847 2009-02-28 13:21:14 2009-02-28 13:21:14 10
1848 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1849 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1850 c1 c2 c3
1851 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1852 c1 c2 c3
1853 2009-03-01 13:21:14 NULL 13
1854 2009-03-02 13:21:14 NULL 14
1855 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1856 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1857 c1 c2 c3
1858 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
1859 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1860 c1 c2 c3
1861 2001-01-01 00:00:00 2009-02-25 12:20:13 11
1862 2009-03-03 13:21:14 2009-03-03 13:21:14 15
1863 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1864 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1865 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1866 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1867 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1868 c1 c2 c3
1869 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1870 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1871 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1872 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1873 c1 c2 c3
1874 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1875 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1876 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1877 c1 c2 c3
1878 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1879 c1 c2 c3
1880 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1881 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1882 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1883 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1884 c1 c2 c3
1885 SELECT count(*) FROM t1;
1886 count(*)
1887 1
1888 TRUNCATE TABLE t1;
1889 SELECT count(*) FROM t1;
1890 count(*)
1891 0
1892 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1893 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1894 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1895 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1896 c1 c2 c3 c1 c2 c3
1897 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1898 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1899 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1900 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1901 c1 c2 c3 c1 c2 c3
1902 TRUNCATE TABLE t1;
1903 TRUNCATE TABLE t2;
1904 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1905 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1906 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1907 c1 c2 c3 c1 c2 c3
1908 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1909 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1910 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1911 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1912 c1 c2 c3 c1 c2 c3
1913 DROP TABLE t1,t2;