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);
5
Note 1265 Data truncated for column 'c2' at row 3
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)
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';
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';
23
UPDATE t1 SET c1='2001-01-03 08:30:00',c2='2001-01-05' WHERE c2='2001-01-04';
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)
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();
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();
36
Note 1265 Data truncated for column 'c1' at row 1
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;
49
UPDATE t1 SET c2='2001-01-10' WHERE c2 IS NULL;
50
SELECT * FROM t1 WHERE c2='2001-01-10';
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';
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';
61
Warning 1265 Data truncated for column 'c2' at row 1
62
UPDATE t1 SET c2='2009-04-31' WHERE c2='2001-01-13';
64
Warning 1265 Data truncated for column 'c2' at row 1
65
UPDATE t1 SET c2='2009-06-31' WHERE c2='2001-01-14';
67
Warning 1265 Data truncated for column 'c2' at row 1
68
UPDATE t1 SET c2='2009-09-31' WHERE c2='2001-01-15';
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 */;
75
Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
76
Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
77
Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
78
Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
79
Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
80
Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
81
UPDATE t1 SET c2='2009-11-31' WHERE c2='2001-01-16';
83
Warning 1265 Data truncated for column 'c2' at row 1
84
SELECT * FROM t1 WHERE c2='0000-00-00';
86
0000-00-00 0000-00-00 7
87
2001-01-12 0000-00-00 8
88
2001-01-13 0000-00-00 9
89
2001-01-14 0000-00-00 10
90
2001-01-15 0000-00-00 11
91
2001-01-16 0000-00-00 12
92
SET SQL_MODE=ALLOW_INVALID_DATES;
93
INSERT INTO t1 VALUES('2001-01-27','2001-01-28',19);
94
UPDATE t1 SET c1='2009-04-31',c2='2009-02-30' WHERE c3=19;
95
SELECT c1,c2,day(c1),day(c2) FROM t1 WHERE c3=19;
97
2009-04-31 2009-02-30 31 30
99
INSERT INTO t1 VALUES('2001-01-17','0000-00-00',13),('2001-01-18','2001-01-18',14);
100
SET SQL_MODE=TRADITIONAL;
101
SELECT * FROM t1 WHERE c2='0000-00-00';
103
0000-00-00 0000-00-00 7
104
2001-01-12 0000-00-00 8
105
2001-01-13 0000-00-00 9
106
2001-01-14 0000-00-00 10
107
2001-01-15 0000-00-00 11
108
2001-01-16 0000-00-00 12
109
2001-01-17 0000-00-00 13
111
Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
112
Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
113
UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
114
ERROR 22007: Incorrect date value: '0000-00-00' for column 'c2' at row 1
115
SET SQL_MODE=DEFAULT;
116
UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
117
UPDATE t1 SET c2='2001-01-18' WHERE c2='0000-00-00' AND c1='2001-01-17';
118
SELECT * FROM t1 WHERE c1='0000-00-00' OR c2='0000-00-00';
120
0000-00-00 0000-00-00 7
121
2001-01-12 0000-00-00 8
122
2001-01-13 0000-00-00 9
123
2001-01-14 0000-00-00 10
124
2001-01-15 0000-00-00 11
125
2001-01-16 0000-00-00 12
126
2001-01-18 0000-00-00 14
127
INSERT INTO t1 VALUES('2001-01-19','2001-01-20',15);
128
UPDATE t1 SET c1='2010-00-01',c2='2010-10-00' WHERE c3=15;
129
SELECT *,month(c1),day(c2) from t1 WHERE c3=15;
130
c1 c2 c3 month(c1) day(c2)
131
2010-00-01 2010-10-00 15 0 0
132
INSERT INTO t1 VALUES('2001-01-21','2001-01-22',16);
133
UPDATE t1 SET c1='1000-01-01',c2='9999-12-31' WHERE c1='2001-01-21';
134
SELECT * FROM t1 WHERE c1='1000-01-01' AND c2='9999-12-31';
136
1000-01-01 9999-12-31 16
137
INSERT INTO t1 VALUES('2001-01-23','2001-01-24',17),('2001-01-25','2001-01-26',18);
138
UPDATE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
139
ERROR 23000: Duplicate entry '2001-01-23' for key 'PRIMARY'
140
UPDATE IGNORE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
141
SELECT count(*) FROM t1 WHERE c1='2001-01-23';
144
UPDATE t1 SET c1=NULL WHERE c2='2001-01-24' /* updates to 0000-00-00 */;
145
ERROR 23000: Duplicate entry '0000-00-00' for key 'PRIMARY'
146
UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24';
148
Warning 1048 Column 'c1' cannot be null
149
SELECT * FROM t1 WHERE c1='0000-00-00';
151
0000-00-00 0000-00-00 7
153
CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
154
INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
155
INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
156
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
158
2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
159
UPDATE t1,t2 SET t2.c1='2005-01-06',t1.c1='2005-01-06' WHERE t2.c1=t1.c1;
160
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
162
2005-01-06 2001-01-01 1 2005-01-06 2001-01-03 1
163
INSERT INTO t1 VALUES('2002-01-01','2002-01-02',3),('2002-01-04','2002-01-02',4);
164
INSERT INTO t2 VALUES('2002-01-02','2002-01-03',3),('2002-01-03','2002-01-04',4);
165
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
167
2002-01-01 2002-01-02 3 2002-01-02 2002-01-03 3
168
2002-01-04 2002-01-02 4 2002-01-02 2002-01-03 3
169
UPDATE t1,t2 SET t2.c1='2002-01-06',t1.c2='2002-01-06' WHERE t2.c1=t1.c2;
170
SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06' AND t1.c2='2002-01-06';
172
2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
173
2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
174
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
176
2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
177
2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
178
DELETE FROM t1 WHERE c2='2002-01-06' AND c3=3;
179
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
181
2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
184
ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, DROP PRIMARY KEY;
185
ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD KEY(c1);
186
UPDATE t1 SET c1='2002-01-07' WHERE c2='2002-01-05';
187
SELECT * FROM t1 WHERE c1='2002-01-07';
189
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);
192
2002-01-08 2002-01-09 4
193
2002-01-10 2002-01-11 5
194
2002-01-12 2002-01-13 6
195
2002-01-14 2002-01-15 7
196
2002-01-16 2002-01-17 8
197
2002-01-18 2002-01-19 9
198
2002-01-20 2002-01-21 10
199
2002-01-22 2002-01-23 11
200
2002-01-24 2002-01-25 12
201
2002-01-26 2002-01-27 13
202
2002-01-28 2002-01-29 14
203
2002-01-30 2002-01-31 15
204
2002-02-01 2002-02-02 16
205
2002-02-03 2002-02-04 17
206
2002-02-05 2002-02-06 18
207
2002-02-07 2002-02-08 19
208
2002-02-09 2002-02-10 20
209
UPDATE t1 SET c1='2003-01-01' WHERE c2 <> '2002-01-09' ORDER BY c2 LIMIT 2;
210
SELECT * FROM t1 WHERE c2 <> '2002-01-09' ORDER BY c2;
212
2003-01-01 2002-01-11 5
213
2003-01-01 2002-01-13 6
214
2002-01-14 2002-01-15 7
215
2002-01-16 2002-01-17 8
216
2002-01-18 2002-01-19 9
217
2002-01-20 2002-01-21 10
218
2002-01-22 2002-01-23 11
219
2002-01-24 2002-01-25 12
220
2002-01-26 2002-01-27 13
221
2002-01-28 2002-01-29 14
222
2002-01-30 2002-01-31 15
223
2002-02-01 2002-02-02 16
224
2002-02-03 2002-02-04 17
225
2002-02-05 2002-02-06 18
226
2002-02-07 2002-02-08 19
227
2002-02-09 2002-02-10 20
228
UPDATE t1 SET c1='2003-01-01' WHERE c2 >= '2002-01-13' ORDER BY c2 DESC LIMIT 2;
229
SELECT * FROM t1 WHERE c2 >= '2002-01-13' ORDER BY c2 DESC;
231
2003-01-01 2002-02-10 20
232
2003-01-01 2002-02-08 19
233
2002-02-05 2002-02-06 18
234
2002-02-03 2002-02-04 17
235
2002-02-01 2002-02-02 16
236
2002-01-30 2002-01-31 15
237
2002-01-28 2002-01-29 14
238
2002-01-26 2002-01-27 13
239
2002-01-24 2002-01-25 12
240
2002-01-22 2002-01-23 11
241
2002-01-20 2002-01-21 10
242
2002-01-18 2002-01-19 9
243
2002-01-16 2002-01-17 8
244
2002-01-14 2002-01-15 7
245
2003-01-01 2002-01-13 6
246
UPDATE t1 SET c1='2003-01-01' WHERE c2 <='2002-01-21' ORDER BY c2 LIMIT 2;
247
SELECT * FROM t1 WHERE c2 <='2002-01-21' ORDER BY c2;
249
2003-01-01 2002-01-09 4
250
2003-01-01 2002-01-11 5
251
2003-01-01 2002-01-13 6
252
2002-01-14 2002-01-15 7
253
2002-01-16 2002-01-17 8
254
2002-01-18 2002-01-19 9
255
2002-01-20 2002-01-21 10
256
UPDATE t1 SET c1='2003-01-01' WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC LIMIT 2;
257
SELECT * FROM t1 WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC;
259
2003-01-01 2002-01-23 11
260
UPDATE t1 SET c1='2003-01-01' WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2 LIMIT 2;
261
SELECT * FROM t1 WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2;
263
2003-01-01 2002-01-25 12
264
2003-01-01 2002-01-27 13
265
2002-01-28 2002-01-29 14
266
UPDATE t1 SET c1='2003-01-01' WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC LIMIT 2;
267
SELECT * FROM t1 WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC;
269
2003-01-01 2002-01-31 15
270
2003-01-01 2002-01-29 14
271
UPDATE t1 SET c1='2003-01-01' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
272
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
274
UPDATE t1 SET c1='2003-01-01' WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2 LIMIT 2;
275
SELECT * FROM t1 WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2;
277
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);
280
2002-01-14 2002-01-15 7
281
2002-01-16 2002-01-17 8
282
2002-01-18 2002-01-19 9
283
2002-01-20 2002-01-21 10
284
2002-02-01 2002-02-02 16
285
2002-02-03 2002-02-04 17
286
2002-03-01 2002-03-01 1
287
2002-03-03 2002-03-04 2
288
2002-03-05 2002-03-06 3
289
2003-01-01 2002-01-09 4
290
2003-01-01 2002-01-11 5
291
2003-01-01 2002-01-13 6
292
2003-01-01 2002-01-23 11
293
2003-01-01 2002-01-25 12
294
2003-01-01 2002-01-27 13
295
2003-01-01 2002-01-29 14
296
2003-01-01 2002-01-31 15
297
2003-01-01 2002-02-06 18
298
2003-01-01 2002-02-08 19
299
2003-01-01 2002-02-10 20
300
2005-01-01 2005-01-02 4
301
2005-01-03 2005-01-04 5
302
2005-01-05 2005-01-06 6
303
2005-01-07 2005-01-08 7
332
SELECT c1 FROM t1 ORDER BY c1 DESC;
359
SELECT * FROM t1 WHERE c1='2003-01-01';
361
2003-01-01 2002-01-09 4
362
2003-01-01 2002-01-11 5
363
2003-01-01 2002-01-13 6
364
2003-01-01 2002-01-23 11
365
2003-01-01 2002-01-25 12
366
2003-01-01 2002-01-27 13
367
2003-01-01 2002-01-29 14
368
2003-01-01 2002-01-31 15
369
2003-01-01 2002-02-06 18
370
2003-01-01 2002-02-08 19
371
2003-01-01 2002-02-10 20
372
SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 LIMIT 2;
374
2002-01-14 2002-01-15 7
375
2002-01-16 2002-01-17 8
376
SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 DESC;
378
2002-01-14 2002-01-15 7
379
2002-01-16 2002-01-17 8
380
2002-01-18 2002-01-19 9
381
2002-01-20 2002-01-21 10
382
2002-02-01 2002-02-02 16
383
2002-02-03 2002-02-04 17
384
2002-03-01 2002-03-01 1
385
2002-03-03 2002-03-04 2
386
2002-03-05 2002-03-06 3
387
2005-01-01 2005-01-02 4
388
2005-01-03 2005-01-04 5
389
2005-01-05 2005-01-06 6
390
2005-01-07 2005-01-08 7
392
SELECT * FROM t1 WHERE c1 > '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
394
2005-01-01 2005-01-02 4
395
2005-01-03 2005-01-04 5
396
SELECT * FROM t1 WHERE c1 >= '2003-01-01' ORDER BY c1,c2;
398
2003-01-01 2002-01-09 4
399
2003-01-01 2002-01-11 5
400
2003-01-01 2002-01-13 6
401
2003-01-01 2002-01-23 11
402
2003-01-01 2002-01-25 12
403
2003-01-01 2002-01-27 13
404
2003-01-01 2002-01-29 14
405
2003-01-01 2002-01-31 15
406
2003-01-01 2002-02-06 18
407
2003-01-01 2002-02-08 19
408
2003-01-01 2002-02-10 20
409
2005-01-01 2005-01-02 4
410
2005-01-03 2005-01-04 5
411
2005-01-05 2005-01-06 6
412
2005-01-07 2005-01-08 7
414
SELECT * FROM t1 WHERE c1 < '2003-01-01' ORDER BY c1,c2 LIMIT 2;
416
2002-01-14 2002-01-15 7
417
2002-01-16 2002-01-17 8
418
SELECT * FROM t1 WHERE c1 <= '2003-01-01' ORDER BY c1,c2 DESC;
420
2002-01-14 2002-01-15 7
421
2002-01-16 2002-01-17 8
422
2002-01-18 2002-01-19 9
423
2002-01-20 2002-01-21 10
424
2002-02-01 2002-02-02 16
425
2002-02-03 2002-02-04 17
426
2002-03-01 2002-03-01 1
427
2002-03-03 2002-03-04 2
428
2002-03-05 2002-03-06 3
429
2003-01-01 2002-02-10 20
430
2003-01-01 2002-02-08 19
431
2003-01-01 2002-02-06 18
432
2003-01-01 2002-01-31 15
433
2003-01-01 2002-01-29 14
434
2003-01-01 2002-01-27 13
435
2003-01-01 2002-01-25 12
436
2003-01-01 2002-01-23 11
437
2003-01-01 2002-01-13 6
438
2003-01-01 2002-01-11 5
439
2003-01-01 2002-01-09 4
440
SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
442
2003-01-01 2002-02-10 20
443
2003-01-01 2002-02-08 19
444
SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2;
446
2003-01-01 2002-01-09 4
447
2003-01-01 2002-01-11 5
448
2003-01-01 2002-01-13 6
449
2003-01-01 2002-01-23 11
450
2003-01-01 2002-01-25 12
451
2003-01-01 2002-01-27 13
452
2003-01-01 2002-01-29 14
453
2003-01-01 2002-01-31 15
454
2003-01-01 2002-02-06 18
455
2003-01-01 2002-02-08 19
456
2003-01-01 2002-02-10 20
457
SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 LIMIT 2;
459
2002-03-01 2002-03-01 1
460
2002-03-03 2002-03-04 2
461
SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 DESC;
463
2002-03-01 2002-03-01 1
464
2002-03-03 2002-03-04 2
465
2002-03-05 2002-03-06 3
466
2003-01-01 2002-02-10 20
467
2003-01-01 2002-02-08 19
468
2003-01-01 2002-02-06 18
469
2003-01-01 2002-01-31 15
470
2003-01-01 2002-01-29 14
471
2003-01-01 2002-01-27 13
472
2003-01-01 2002-01-25 12
473
2003-01-01 2002-01-23 11
474
2003-01-01 2002-01-13 6
475
2003-01-01 2002-01-11 5
476
2003-01-01 2002-01-09 4
477
SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2 DESC LIMIT 2;
479
2003-01-01 2002-02-10 20
480
2003-01-01 2002-02-08 19
481
SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2;
483
2003-01-01 2002-01-09 4
484
2003-01-01 2002-01-11 5
485
2003-01-01 2002-01-13 6
486
2003-01-01 2002-01-23 11
487
2003-01-01 2002-01-25 12
488
2003-01-01 2002-01-27 13
489
2003-01-01 2002-01-29 14
490
2003-01-01 2002-01-31 15
491
2003-01-01 2002-02-06 18
492
2003-01-01 2002-02-08 19
493
2003-01-01 2002-02-10 20
494
2005-01-07 2005-01-08 7
495
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
498
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
501
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
503
2003-01-01 2002-01-09 4
504
2003-01-01 2002-01-11 5
505
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
507
2003-01-01 2002-01-09 4
508
2003-01-01 2002-01-11 5
509
2003-01-01 2002-01-13 6
510
2002-01-14 2002-01-15 7
511
2002-01-16 2002-01-17 8
512
2002-01-18 2002-01-19 9
513
2002-01-20 2002-01-21 10
514
2003-01-01 2002-01-23 11
515
2003-01-01 2002-01-25 12
516
2003-01-01 2002-01-27 13
517
2003-01-01 2002-01-29 14
518
2003-01-01 2002-01-31 15
519
2002-02-01 2002-02-02 16
520
2002-02-03 2002-02-04 17
521
2003-01-01 2002-02-06 18
522
2003-01-01 2002-02-08 19
523
2003-01-01 2002-02-10 20
524
2002-03-01 2002-03-01 1
525
2002-03-03 2002-03-04 2
526
2002-03-05 2002-03-06 3
527
2005-01-01 2005-01-02 4
528
2005-01-03 2005-01-04 5
529
2005-01-05 2005-01-06 6
530
2005-01-07 2005-01-08 7
531
SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1 DESC LIMIT 2;
533
2003-01-01 2002-01-09 4
534
2003-01-01 2002-01-11 5
535
SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1;
537
2003-01-01 2002-01-09 4
538
2003-01-01 2002-01-11 5
539
2003-01-01 2002-01-13 6
540
2003-01-01 2002-01-23 11
541
2003-01-01 2002-01-25 12
542
2003-01-01 2002-01-27 13
543
2003-01-01 2002-01-29 14
544
2003-01-01 2002-01-31 15
545
2003-01-01 2002-02-06 18
546
2003-01-01 2002-02-08 19
547
2003-01-01 2002-02-10 20
548
2005-01-01 2005-01-02 4
549
2005-01-03 2005-01-04 5
550
2005-01-05 2005-01-06 6
551
2005-01-07 2005-01-08 7
552
SELECT * FROM t1 WHERE c1='2003-01-03' OR c2='2002-03-06';
554
2002-03-05 2002-03-06 3
555
UPDATE t1 SET c1='2006-01-01' WHERE c1='2003-01-03' OR c2='2002-03-06';
556
SELECT * FROM t1 WHERE c1='2006-01-01' OR c2='2002-03-04';
558
2002-03-03 2002-03-04 2
559
2006-01-01 2002-03-06 3
561
ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD PRIMARY KEY(c1);
562
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);
564
Note 1265 Data truncated for column 'c1' at row 1
565
Note 1265 Data truncated for column 'c2' at row 1
566
Note 1265 Data truncated for column 'c1' at row 2
567
Note 1265 Data truncated for column 'c2' at row 2
568
Note 1265 Data truncated for column 'c1' at row 3
569
Note 1265 Data truncated for column 'c2' at row 3
570
Note 1265 Data truncated for column 'c1' at row 4
571
Note 1265 Data truncated for column 'c2' at row 4
572
INSERT INTO t1 VALUES('1000-01-01','1000-01-01',1),('9999-12-31','9999-12-31',2);
573
SELECT * FROM t1 WHERE c1 <='1000-01-01';
575
1000-01-01 1000-01-01 1
576
DELETE FROM t1 WHERE c1 <='1000-01-01';
577
SELECT * FROM t1 WHERE c1 <='1000-01-01';
579
SELECT * FROM t1 WHERE c1 >='9999-12-31';
581
9999-12-31 9999-12-31 2
582
DELETE FROM t1 WHERE c1 >='9999-12-31';
583
SELECT * FROM t1 WHERE c1 >='9999-12-31';
585
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
587
2009-02-28 2009-02-28 6
588
2009-02-27 2009-02-27 5
589
DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
590
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
592
SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
594
2009-02-26 2009-02-26 4
595
DELETE FROM t1 WHERE c1 <> CURDATE() ORDER BY c1 LIMIT 2;
596
SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
598
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);
600
Note 1265 Data truncated for column 'c2' at row 1
601
Note 1265 Data truncated for column 'c1' at row 2
602
Note 1265 Data truncated for column 'c2' at row 2
603
Note 1265 Data truncated for column 'c1' at row 3
604
Note 1265 Data truncated for column 'c2' at row 3
605
Note 1265 Data truncated for column 'c1' at row 4
606
Note 1265 Data truncated for column 'c2' at row 4
607
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
609
2001-01-01 2009-02-25 7
610
2009-02-25 2009-02-25 3
611
2009-02-26 2009-02-26 8
612
2009-02-27 2009-02-27 9
613
DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
614
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
616
2009-02-26 2009-02-26 8
617
2009-02-27 2009-02-27 9
618
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
620
DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
621
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
623
SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
625
2009-02-26 2009-02-26 8
627
Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
628
Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
629
DELETE FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
631
Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
632
SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
635
Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
636
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);
638
Note 1265 Data truncated for column 'c2' at row 1
639
Note 1265 Data truncated for column 'c1' at row 2
640
Note 1265 Data truncated for column 'c2' at row 2
641
Note 1265 Data truncated for column 'c1' at row 3
642
Note 1265 Data truncated for column 'c1' at row 4
643
SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
645
2009-02-26 2009-02-26 12
646
2009-02-27 2009-02-27 9
647
DELETE FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
648
SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
650
SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
652
2009-02-28 2009-02-28 10
654
Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
655
DELETE FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
657
Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
658
SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
661
Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
662
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
666
DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
667
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
669
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);
671
Note 1265 Data truncated for column 'c1' at row 1
672
Note 1265 Data truncated for column 'c2' at row 1
673
Note 1265 Data truncated for column 'c1' at row 2
674
Note 1265 Data truncated for column 'c1' at row 3
675
Note 1265 Data truncated for column 'c2' at row 3
676
Note 1265 Data truncated for column 'c1' at row 4
677
Note 1265 Data truncated for column 'c2' at row 4
678
Note 1265 Data truncated for column 'c1' at row 5
679
Note 1265 Data truncated for column 'c2' at row 5
680
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
682
2001-01-01 2009-02-25 11
683
2009-03-03 2009-03-03 15
684
2009-03-05 2009-03-05 17
685
2009-03-06 2009-03-06 18
686
2009-03-07 2009-03-07 19
687
DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
688
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
690
2009-03-05 2009-03-05 17
691
2009-03-06 2009-03-06 18
692
2009-03-07 2009-03-07 19
693
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
695
2009-03-05 2009-03-05 17
696
DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1 LIMIT 2;
697
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
699
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
701
2009-03-06 2009-03-06 18
702
2009-03-07 2009-03-07 19
703
Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
704
Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
706
DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
708
Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
709
Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
710
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
712
SELECT count(*) FROM t1;
716
SELECT count(*) FROM t1;
719
CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
720
INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
721
INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
722
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
724
2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
725
DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c1;
726
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
730
INSERT INTO t1 VALUES('2001-01-01','2001-01-02',1),('2001-01-03','2001-01-02',2);
731
INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
732
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
734
2001-01-01 2001-01-02 1 2001-01-02 2001-01-03 1
735
2001-01-03 2001-01-02 2 2001-01-02 2001-01-03 1
736
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
737
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
740
CREATE TABLE t1(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME NULL, c3 INT, INDEX idx2(c2));
741
INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
742
UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
743
SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
744
c1 hour(c1) minute(c1) second(c1)
745
2001-01-01 00:00:00 0 0 0
746
INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
747
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';
748
SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
749
c1 ADDTIME(c2,'1 1:1:1.000002')
750
2009-01-01 01:02:03 2009-01-04 01:01:00.000002
751
INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
752
SET TIMESTAMP=1235553613;
753
UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
754
UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
757
2001-01-01 00:00:00 2010-01-01 14:12:33 1
758
2009-01-01 01:02:03 2009-01-02 23:59:59 2
759
2009-02-26 01:01:01 2009-02-25 12:20:13 3
760
INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
761
SELECT * FROM t1 WHERE c2 IS NULL;
763
2001-01-08 00:00:00 NULL 5
764
UPDATE t1 SET c2=NULL WHERE c1='2001-01-06 00:00:00';
765
SELECT * FROM t1 WHERE c2 IS NULL;
767
2001-01-06 00:00:00 NULL 4
768
2001-01-08 00:00:00 NULL 5
769
UPDATE t1 SET c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
770
SELECT * FROM t1 WHERE c2='2001-01-10';
772
2001-01-06 00:00:00 2001-01-10 00:00:00 4
773
2001-01-08 00:00:00 2001-01-10 00:00:00 5
774
INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
775
UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
777
Warning 1264 Out of range value for column 'c1' at row 1
778
Warning 1264 Out of range value for column 'c2' at row 1
779
UPDATE t1 SET c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
781
Warning 1264 Out of range value for column 'c2' at row 1
782
SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
786
Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
787
Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59' for column 'c2' at row 1
788
Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
789
Warning 1292 Truncated incorrect datetime value: '2001-01-09 24:59:59'
790
Warning 1292 Truncated incorrect datetime value: '2009-01-10 23:60:59'
791
SELECT count(*) FROM t1 WHERE c2='2001-01-11 23:59:60' /* returns 0 */;
795
Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
796
Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
797
SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
799
0000-00-00 00:00:00 0000-00-00 00:00:00 6
800
2001-01-11 00:00:00 0000-00-00 00:00:00 7
801
INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
802
SET SQL_MODE=TRADITIONAL;
803
SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
805
0000-00-00 00:00:00 0000-00-00 00:00:00 6
806
2001-01-11 00:00:00 0000-00-00 00:00:00 7
807
2001-01-17 00:00:00 0000-00-00 00:00:00 8
809
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
810
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
811
UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
812
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
813
SET SQL_MODE=DEFAULT;
814
UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
815
UPDATE t1 SET c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
816
SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
818
0000-00-00 00:00:00 0000-00-00 00:00:00 6
819
2001-01-11 00:00:00 0000-00-00 00:00:00 7
820
2001-01-18 00:00:00 0000-00-00 00:00:00 9
821
SELECT * FROM t1 WHERE c2='2001-01-17 12:34:00';
823
2001-01-17 00:00:00 2001-01-17 12:34:00 8
824
INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
825
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';
826
SELECT * FROM t1 WHERE c1='1000-01-01 00:00:00' AND c2='9999-12-31 23:59:59';
828
1000-01-01 00:00:00 9999-12-31 23:59:59 10
829
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);
830
UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
831
ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
832
UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
833
SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
836
UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to 0000-00-00 00:00:00 */;
837
ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'
838
UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24 03:33';
840
Warning 1048 Column 'c1' cannot be null
841
SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00';
843
0000-00-00 00:00:00 0000-00-00 00:00:00 6
845
CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
846
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);
847
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);
848
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
850
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
851
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;
852
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
854
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
855
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);
856
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);
857
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
859
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
860
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
861
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;
862
SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
864
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
865
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
866
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
868
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
869
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
870
DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
871
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
873
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
876
ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, DROP PRIMARY KEY;
877
ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD KEY(c1);
878
UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
879
SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
881
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);
884
2002-01-08 01:30:00 2002-01-09 01:30:00 4
885
2002-01-08 01:30:05 2002-01-09 01:30:05 5
886
2002-01-08 01:30:10 2002-01-09 01:30:10 6
887
2002-01-08 01:30:15 2002-01-09 01:30:15 7
888
2002-01-08 01:30:20 2002-01-09 01:30:20 8
889
2002-01-08 01:30:25 2002-01-09 01:30:25 9
890
2002-01-08 01:30:30 2002-01-09 01:30:30 10
891
2002-01-08 01:30:35 2002-01-09 01:30:35 11
892
2002-01-08 01:30:40 2002-01-09 01:30:40 12
893
2002-01-08 01:30:45 2002-01-09 01:30:45 13
894
2002-01-08 01:30:50 2002-01-09 01:30:50 14
895
2002-01-08 01:30:55 2002-01-09 01:30:55 15
896
2002-01-08 01:30:59 2002-01-09 01:30:59 16
897
2002-01-08 02:00:00 2002-01-09 02:00:00 17
898
2002-01-08 02:00:05 2002-01-09 02:00:05 18
899
2002-01-08 02:00:10 2002-01-09 02:00:10 19
900
2002-01-08 02:00:15 2002-01-09 02:00:15 20
901
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
902
SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
904
2003-01-01 01:02:03 2002-01-09 01:30:05 5
905
2003-01-01 01:02:03 2002-01-09 01:30:10 6
906
2002-01-08 01:30:15 2002-01-09 01:30:15 7
907
2002-01-08 01:30:20 2002-01-09 01:30:20 8
908
2002-01-08 01:30:25 2002-01-09 01:30:25 9
909
2002-01-08 01:30:30 2002-01-09 01:30:30 10
910
2002-01-08 01:30:35 2002-01-09 01:30:35 11
911
2002-01-08 01:30:40 2002-01-09 01:30:40 12
912
2002-01-08 01:30:45 2002-01-09 01:30:45 13
913
2002-01-08 01:30:50 2002-01-09 01:30:50 14
914
2002-01-08 01:30:55 2002-01-09 01:30:55 15
915
2002-01-08 01:30:59 2002-01-09 01:30:59 16
916
2002-01-08 02:00:00 2002-01-09 02:00:00 17
917
2002-01-08 02:00:05 2002-01-09 02:00:05 18
918
2002-01-08 02:00:10 2002-01-09 02:00:10 19
919
2002-01-08 02:00:15 2002-01-09 02:00:15 20
920
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
921
SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
923
2003-01-01 01:02:03 2002-01-09 02:00:15 20
924
2003-01-01 01:02:03 2002-01-09 02:00:10 19
925
2002-01-08 02:00:05 2002-01-09 02:00:05 18
926
2002-01-08 02:00:00 2002-01-09 02:00:00 17
927
2002-01-08 01:30:59 2002-01-09 01:30:59 16
928
2002-01-08 01:30:55 2002-01-09 01:30:55 15
929
2002-01-08 01:30:50 2002-01-09 01:30:50 14
930
2002-01-08 01:30:45 2002-01-09 01:30:45 13
931
2002-01-08 01:30:40 2002-01-09 01:30:40 12
932
2002-01-08 01:30:35 2002-01-09 01:30:35 11
933
2002-01-08 01:30:30 2002-01-09 01:30:30 10
934
2002-01-08 01:30:25 2002-01-09 01:30:25 9
935
2002-01-08 01:30:20 2002-01-09 01:30:20 8
936
2002-01-08 01:30:15 2002-01-09 01:30:15 7
937
2003-01-01 01:02:03 2002-01-09 01:30:10 6
938
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
939
SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
941
2003-01-01 01:02:03 2002-01-09 01:30:00 4
942
2003-01-01 01:02:03 2002-01-09 01:30:05 5
943
2003-01-01 01:02:03 2002-01-09 01:30:10 6
944
2002-01-08 01:30:15 2002-01-09 01:30:15 7
945
2002-01-08 01:30:20 2002-01-09 01:30:20 8
946
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
947
SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
949
2003-01-01 01:02:03 2002-01-09 01:30:30 10
950
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;
952
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
953
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
954
Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
955
SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
958
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
959
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
960
Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
961
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;
962
SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
964
2003-01-01 01:02:03 2002-01-09 02:00:00 17
965
2003-01-01 01:02:03 2002-01-09 01:30:55 15
966
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
967
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
969
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;
970
SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
972
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);
975
0000-00-00 00:00:01 NULL 5
976
0005-10-10 01:02:03 0005-10-10 01:02:03 4
977
2002-01-08 01:30:15 2002-01-09 01:30:15 7
978
2002-01-08 01:30:20 2002-01-09 01:30:20 8
979
2002-01-08 01:30:25 2002-01-09 01:30:25 9
980
2002-01-08 01:30:35 2002-01-09 01:30:35 11
981
2002-01-08 01:30:40 2002-01-09 01:30:40 12
982
2002-01-08 01:30:45 2002-01-09 01:30:45 13
983
2002-01-08 01:30:50 2002-01-09 01:30:50 14
984
2002-01-08 01:30:59 2002-01-09 01:30:59 16
985
2002-01-08 02:00:05 2002-01-09 02:00:05 18
986
2002-03-01 00:00:01 2002-03-02 00:00:01 1
987
2002-03-03 01:00:30 2002-03-04 01:00:30 2
988
2003-01-01 01:02:03 2002-01-09 01:30:00 4
989
2003-01-01 01:02:03 2002-01-09 01:30:05 5
990
2003-01-01 01:02:03 2002-01-09 01:30:10 6
991
2003-01-01 01:02:03 2002-01-09 01:30:30 10
992
2003-01-01 01:02:03 2002-01-09 01:30:55 15
993
2003-01-01 01:02:03 2002-01-09 02:00:00 17
994
2003-01-01 01:02:03 2002-01-09 02:00:10 19
995
2003-01-01 01:02:03 2002-01-09 02:00:15 20
996
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1021
SELECT c1 FROM t1 ORDER BY c1 DESC;
1045
SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1047
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1048
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1049
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1050
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1051
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1052
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1053
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1054
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1055
SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1057
0000-00-00 00:00:01 NULL 5
1058
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1059
SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1061
0000-00-00 00:00:01 NULL 5
1062
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1063
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1064
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1065
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1066
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1067
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1068
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1069
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1070
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1071
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1072
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1073
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1074
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1075
SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1077
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1078
SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1080
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1081
SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1083
0000-00-00 00:00:01 NULL 5
1084
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1085
SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1087
0000-00-00 00:00:01 NULL 5
1088
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1089
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1090
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1091
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1092
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1093
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1094
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1095
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1096
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1097
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1098
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1099
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1100
SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1102
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1103
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1104
SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1106
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1107
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1108
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1109
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1110
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1111
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1112
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1113
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1114
SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1116
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1117
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1118
SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1120
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1121
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1122
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1123
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1124
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1125
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1126
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1127
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1128
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1129
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1130
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;
1132
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1133
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1134
SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1136
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1137
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1138
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1139
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1140
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1141
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1142
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1143
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1144
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1145
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1147
0000-00-00 00:00:01 NULL 5
1148
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1150
0000-00-00 00:00:01 NULL 5
1151
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1153
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1154
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1155
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1157
0005-10-10 01:02:03 0005-10-10 01:02:03 4
1158
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1159
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1160
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1161
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1162
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1163
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1164
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1165
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1166
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1167
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1168
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1169
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1170
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1171
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1172
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1173
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1174
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1175
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1176
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1177
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1178
SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1180
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1181
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1182
SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1184
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1185
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1186
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1187
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1188
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1189
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1190
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1191
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1192
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1193
SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1195
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1196
UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1197
SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1199
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1200
2006-01-01 00:00:01 2002-03-02 00:00:01 1
1202
ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD PRIMARY KEY(c1);
1203
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);
1204
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);
1205
SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1207
1000-01-01 00:00:00 1000-01-01 00:00:00 1
1208
DELETE FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1209
SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1211
SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1213
9999-12-31 23:59:59 9999-12-31 23:59:59 2
1214
DELETE FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1215
SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1217
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1219
2009-02-28 13:21:14 2009-02-28 13:21:14 6
1220
2009-02-27 13:21:14 2009-02-27 13:21:14 5
1221
DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1222
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1224
SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1226
2009-02-26 13:21:14 2009-02-26 13:21:14 4
1227
DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1228
SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1230
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);
1231
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1233
2001-01-01 00:00:00 2009-02-25 12:20:13 7
1234
2009-02-25 12:20:13 2009-02-25 12:20:13 3
1235
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1236
DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1237
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1239
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1240
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1242
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1243
DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1244
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1246
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1248
DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1249
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1251
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);
1252
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1254
2009-02-26 13:21:14 2009-02-26 13:21:14 12
1255
2009-02-27 13:21:14 2009-02-27 13:21:14 9
1256
DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1257
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1259
SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1261
2009-02-28 13:21:14 2009-02-28 13:21:14 10
1262
DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1263
SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1265
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1267
2009-03-01 13:21:14 NULL 13
1268
2009-03-02 13:21:14 NULL 14
1269
DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1270
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1272
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);
1273
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1275
2001-01-01 00:00:00 2009-02-25 12:20:13 11
1276
2009-03-03 13:21:14 2009-03-03 13:21:14 15
1277
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1278
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1279
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1280
DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1281
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1283
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1284
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1285
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1286
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1288
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1289
DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1290
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1292
SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1294
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1295
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1296
DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1297
SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1299
SELECT count(*) FROM t1;
1303
SELECT count(*) FROM t1;
1306
CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
1307
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);
1308
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);
1309
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1311
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
1312
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
1313
DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1314
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1318
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);
1319
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);
1320
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1322
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
1323
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
1324
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1325
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1328
CREATE TABLE mt1(id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1329
INSERT INTO mt1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
1330
CREATE TABLE mt2 (id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1331
INSERT INTO mt2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
1332
CREATE TABLE mt3 (id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id));
1333
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');
1334
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;
1337
1 2002-02-04 00:00:00
1338
5 2002-05-12 00:00:00
1339
6 2002-06-22 00:00:00
1340
7 2002-07-22 00:00:00
1341
DROP TABLE mt1,mt2,mt3;
1342
CREATE TABLE t1(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT, INDEX idx2(c2));
1343
INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
1344
UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
1345
SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
1346
c1 hour(c1) minute(c1) second(c1)
1347
2001-01-01 00:00:00 0 0 0
1348
INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
1349
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';
1350
SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
1351
c1 ADDTIME(c2,'1 1:1:1.000002')
1352
2009-01-01 01:02:03 2009-01-04 01:01:00.000002
1353
INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
1354
SET TIMESTAMP=1235553613;
1355
UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE() /* updates c1 with current timestamp */;
1356
UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
1359
2001-01-01 00:00:00 2010-01-01 14:12:33 1
1360
2009-01-01 01:02:03 2009-01-02 23:59:59 2
1361
2009-02-26 01:01:01 2009-02-25 12:20:13 3
1362
INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
1363
UPDATE t1 SET c1='20010106010030',c2=NULL WHERE c1='2001-01-06 00:00:00';
1364
SELECT c1,c2 FROM t1 WHERE c2 IS NULL;
1366
2001-01-06 01:00:30 NULL
1367
2001-01-08 00:00:00 NULL
1370
2001-01-01 00:00:00 2010-01-01 14:12:33 1
1371
2001-01-06 01:00:30 NULL 4
1372
2001-01-08 00:00:00 NULL 5
1373
2009-01-01 01:02:03 2009-01-02 23:59:59 2
1374
2009-02-26 01:01:01 2009-02-25 12:20:13 3
1375
UPDATE t1 SET c1=c1,c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
1376
SELECT * FROM t1 WHERE c2='2001-01-10';
1378
2001-01-06 01:00:30 2001-01-10 00:00:00 4
1379
2001-01-08 00:00:00 2001-01-10 00:00:00 5
1380
INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
1381
UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
1383
Warning 1265 Data truncated for column 'c1' at row 1
1384
Warning 1265 Data truncated for column 'c2' at row 1
1385
UPDATE t1 SET c1='2001-01-11 23:59:59',c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
1387
Warning 1265 Data truncated for column 'c2' at row 1
1388
SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
1392
Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
1393
Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59' for column 'c2' at row 1
1394
Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
1395
Warning 1292 Truncated incorrect datetime value: '2001-01-09 24:59:59'
1396
Warning 1292 Truncated incorrect datetime value: '2009-01-10 23:60:59'
1397
SELECT count(*) FROM t1 WHERE c1='2001-01-11 23:59:59' AND c2='2001-01-11 23:59:60';
1401
Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
1402
Warning 1292 Truncated incorrect datetime value: '2001-01-11 23:59:60'
1403
SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1405
0000-00-00 00:00:00 0000-00-00 00:00:00 6
1406
2001-01-11 23:59:59 0000-00-00 00:00:00 7
1407
INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
1408
SET SQL_MODE=TRADITIONAL;
1409
SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
1411
0000-00-00 00:00:00 0000-00-00 00:00:00 6
1412
2001-01-11 23:59:59 0000-00-00 00:00:00 7
1413
2001-01-17 00:00:00 0000-00-00 00:00:00 8
1415
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1416
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1417
UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1418
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1419
SET SQL_MODE=DEFAULT;
1420
UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1421
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';
1422
SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1424
0000-00-00 00:00:00 0000-00-00 00:00:00 6
1425
1972-01-01 07:35:00 0000-00-00 00:00:00 9
1426
2001-01-11 23:59:59 0000-00-00 00:00:00 7
1427
SELECT * FROM t1 WHERE c1='2001-01-17 12:34:00' AND c2='2001-01-17 12:34:00';
1429
2001-01-17 12:34:00 2001-01-17 12:34:00 8
1430
INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
1431
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';
1432
SELECT * FROM t1 WHERE c1='1971-01-01 00:00:01' AND c2='2038-01-09 03:14:07';
1434
1971-01-01 00:00:01 2038-01-09 03:14:07 10
1435
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);
1436
UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1437
ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
1438
UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1439
SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
1442
UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to current timestamp */;
1443
UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-26 05:33';
1444
SELECT * FROM t1 WHERE c1=CURRENT_TIMESTAMP();
1446
2009-02-25 12:20:13 2001-01-24 03:33:00 11
1448
CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1449
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);
1450
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);
1451
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1453
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
1454
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;
1455
SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1457
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
1458
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);
1459
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);
1460
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1462
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
1463
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
1464
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;
1465
SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
1467
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
1468
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
1469
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1471
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
1472
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
1473
DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
1474
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1476
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
1479
ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, DROP PRIMARY KEY;
1480
ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD KEY(c1);
1481
UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
1482
SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
1484
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);
1487
2002-01-08 01:30:00 2002-01-09 01:30:00 4
1488
2002-01-08 01:30:05 2002-01-09 01:30:05 5
1489
2002-01-08 01:30:10 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 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
1505
SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
1507
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1508
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1509
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1510
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1511
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1512
2002-01-08 01:30:30 2002-01-09 01:30:30 10
1513
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1514
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1515
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1516
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1517
2002-01-08 01:30:55 2002-01-09 01:30:55 15
1518
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1519
2002-01-08 02:00:00 2002-01-09 02:00:00 17
1520
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1521
2002-01-08 02:00:10 2002-01-09 02:00:10 19
1522
2002-01-08 02:00:15 2002-01-09 02:00:15 20
1523
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
1524
SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
1526
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1527
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1528
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1529
2002-01-08 02:00:00 2002-01-09 02:00:00 17
1530
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1531
2002-01-08 01:30:55 2002-01-09 01:30:55 15
1532
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1533
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1534
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1535
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1536
2002-01-08 01:30:30 2002-01-09 01:30:30 10
1537
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1538
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1539
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1540
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1541
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
1542
SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
1544
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1545
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1546
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1547
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1548
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1549
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
1550
SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
1552
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1553
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;
1555
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1556
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1557
Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
1558
SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
1561
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1562
Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1563
Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
1564
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;
1565
SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
1567
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1568
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1569
UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1570
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
1572
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;
1573
SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
1575
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);
1577
Warning 1264 Out of range value for column 'c1' at row 4
1578
Warning 1264 Out of range value for column 'c2' at row 4
1579
Warning 1265 Data truncated for column 'c1' at row 5
1582
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1583
0000-00-00 00:00:00 NULL 5
1584
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1585
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1586
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1587
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1588
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1589
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1590
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1591
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1592
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1593
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1594
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1595
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1596
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1597
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1598
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1599
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1600
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1601
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1602
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1603
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1628
SELECT c1 FROM t1 ORDER BY c1 DESC;
1652
SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1654
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1655
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1656
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1657
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1658
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1659
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1660
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1661
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1662
SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1664
0000-00-00 00:00:00 NULL 5
1665
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1666
SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1668
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1669
0000-00-00 00:00:00 NULL 5
1670
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1671
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1672
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1673
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1674
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1675
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1676
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1677
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1678
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1679
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1680
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1681
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1682
SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1684
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1685
SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1687
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1688
SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1690
0000-00-00 00:00:00 NULL 5
1691
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1692
SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1694
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1695
0000-00-00 00:00:00 NULL 5
1696
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1697
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1698
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1699
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1700
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1701
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1702
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1703
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1704
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1705
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1706
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1707
SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1709
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1710
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1711
SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1713
2003-01-01 01:02:03 2002-01-09 01:30:00 4
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:10 6
1716
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1717
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1718
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1719
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1720
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1721
SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1723
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1724
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1725
SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1727
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1728
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1729
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1730
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1731
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1732
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1733
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1734
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1735
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1736
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1737
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;
1739
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1740
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1741
SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1743
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1744
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1745
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1746
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1747
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1748
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1749
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1750
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1751
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1752
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1754
0000-00-00 00:00:00 NULL 5
1755
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1757
0000-00-00 00:00:00 NULL 5
1758
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1760
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1761
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1762
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1764
0000-00-00 00:00:00 0000-00-00 00:00:00 4
1765
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1766
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1767
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1768
2002-01-08 01:30:15 2002-01-09 01:30:15 7
1769
2002-01-08 01:30:20 2002-01-09 01:30:20 8
1770
2002-01-08 01:30:25 2002-01-09 01:30:25 9
1771
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1772
2002-01-08 01:30:35 2002-01-09 01:30:35 11
1773
2002-01-08 01:30:40 2002-01-09 01:30:40 12
1774
2002-01-08 01:30:45 2002-01-09 01:30:45 13
1775
2002-01-08 01:30:50 2002-01-09 01:30:50 14
1776
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1777
2002-01-08 01:30:59 2002-01-09 01:30:59 16
1778
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1779
2002-01-08 02:00:05 2002-01-09 02:00:05 18
1780
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1781
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1782
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1783
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1784
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1785
SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1787
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1788
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1789
SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1791
2003-01-01 01:02:03 2002-01-09 01:30:00 4
1792
2003-01-01 01:02:03 2002-01-09 01:30:05 5
1793
2003-01-01 01:02:03 2002-01-09 01:30:10 6
1794
2003-01-01 01:02:03 2002-01-09 01:30:30 10
1795
2003-01-01 01:02:03 2002-01-09 01:30:55 15
1796
2003-01-01 01:02:03 2002-01-09 02:00:00 17
1797
2003-01-01 01:02:03 2002-01-09 02:00:10 19
1798
2003-01-01 01:02:03 2002-01-09 02:00:15 20
1799
2005-01-03 00:00:01 2005-01-04 00:00:01 3
1800
SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1802
2002-03-01 00:00:01 2002-03-02 00:00:01 1
1803
UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1804
SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1806
2002-03-03 01:00:30 2002-03-04 01:00:30 2
1807
2006-01-01 00:00:01 2002-03-02 00:00:01 1
1809
ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD PRIMARY KEY(c1);
1810
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);
1811
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);
1812
SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1814
1971-01-01 00:00:01 1971-01-01 00:00:01 1
1815
DELETE FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1816
SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1818
SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1820
2038-01-09 03:14:07 2038-01-09 03:14:07 2
1821
DELETE FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1822
SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1824
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1826
2009-02-28 13:21:14 2009-02-28 13:21:14 6
1827
2009-02-27 13:21:14 2009-02-27 13:21:14 5
1828
DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1829
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1831
SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1833
2009-02-26 13:21:14 2009-02-26 13:21:14 4
1834
DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1835
SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1837
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);
1838
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1840
2001-01-01 00:00:00 2009-02-25 12:20:13 7
1841
2009-02-25 12:20:13 2009-02-25 12:20:13 3
1842
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1843
DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1844
SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1846
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1847
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1849
2009-02-26 13:21:14 2009-02-26 13:21:14 8
1850
DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1851
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1853
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1855
DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1856
SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1858
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);
1859
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1861
2009-02-26 13:21:14 2009-02-26 13:21:14 12
1862
2009-02-27 13:21:14 2009-02-27 13:21:14 9
1863
DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1864
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1866
SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1868
2009-02-28 13:21:14 2009-02-28 13:21:14 10
1869
DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1870
SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1872
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1874
2009-03-01 13:21:14 NULL 13
1875
2009-03-02 13:21:14 NULL 14
1876
DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1877
SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1879
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);
1880
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1882
2001-01-01 00:00:00 2009-02-25 12:20:13 11
1883
2009-03-03 13:21:14 2009-03-03 13:21:14 15
1884
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1885
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1886
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1887
DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1888
SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1890
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1891
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1892
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1893
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1895
2009-03-05 13:21:14 2009-03-05 13:21:14 17
1896
DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1897
SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1899
SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1901
2009-03-06 13:21:14 2009-03-06 13:21:14 18
1902
2009-03-07 13:21:14 2009-03-07 13:21:14 19
1903
DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1904
SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1906
SELECT count(*) FROM t1;
1910
SELECT count(*) FROM t1;
1913
CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1914
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);
1915
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);
1916
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1918
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
1919
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
1920
DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1921
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1925
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);
1926
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);
1927
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1929
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
1930
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
1931
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1932
SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;