func_time.result 46.6 KB
Newer Older
1
drop table if exists t1,t2,t3;
2
set time_zone="+03:00";
3
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");
4 5
from_days(to_days("960101"))	to_days(960201)-to_days("19960101")	to_days(date_add(curdate(), interval 1 day))-to_days(curdate())	weekday("1997-11-29")
1996-01-01	31	1	5
6
select period_add("9602",-12),period_diff(199505,"9404") ;
7 8
period_add("9602",-12)	period_diff(199505,"9404")
199502	13
9
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
10
now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
11
0.000000	0	0
12
select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
13
from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
14
1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112.000000
15 16
select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
sec_to_time(time_to_sec("0:30:47")/6.21);
17
sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
18
02:30:01	23001.000000	54742	00:04:57
19 20 21
select sec_to_time(time_to_sec('-838:59:59'));
sec_to_time(time_to_sec('-838:59:59'))
-838:59:59
22
select now()-curdate()*1000000-curtime();
23
now()-curdate()*1000000-curtime()
24
0.000000
25
select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
26 27
strcmp(current_timestamp(),concat(current_date()," ",current_time()))
0
28 29 30 31 32 33
select strcmp(localtime(),concat(current_date()," ",current_time()));
strcmp(localtime(),concat(current_date()," ",current_time()))
0
select strcmp(localtimestamp(),concat(current_date()," ",current_time()));
strcmp(localtimestamp(),concat(current_date()," ",current_time()))
0
34
select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
35 36
date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")
January Thursday 2nd 1997 97 01 02 03 04 05 4
37
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
38 39
date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"))
January Thursday 2nd 1997 97 01 02 12 00 00 4
40
select dayofmonth("1997-01-02"),dayofmonth(19970323);
41 42
dayofmonth("1997-01-02")	dayofmonth(19970323)
2	23
43
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
44 45
month("1997-01-02")	year("98-02-03")	dayofyear("1997-12-31")
1	1998	365
46
select month("2001-02-00"),year("2001-00-00");
47 48
month("2001-02-00")	year("2001-00-00")
2	2001
49
select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);
50 51
DAYOFYEAR("1997-03-03")	WEEK("1998-03-03")	QUARTER(980303)
62	9	1
52
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
53 54
HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
23	3	22
55
select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
56
week(19980101)	week(19970101)	week(19980101,1)	week(19970101,1)
57 58
0	0	1	1
select week(19981231),week(19971231),week(19981231,1),week(19971231,1);
59
week(19981231)	week(19971231)	week(19981231,1)	week(19971231,1)
60 61
52	52	53	53
select week(19950101),week(19950101,1);
62 63
week(19950101)	week(19950101,1)
1	0
64
select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);
65 66
yearweek('1981-12-31',1)	yearweek('1982-01-01',1)	yearweek('1982-12-31',1)	yearweek('1983-01-01',1)
198153	198153	198252	198252
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
select yearweek('1987-01-01',1),yearweek('1987-01-01');
yearweek('1987-01-01',1)	yearweek('1987-01-01')
198701	198652
select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006';
2000	2001	2002	2003	2004	2005	2006
0	0	0	0	0	0	1
select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006';
2000	2001	2002	2003	2004	2005	2006
1	0	1	1	1	1	1
select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006';
2000	2001	2002	2003	2004	2005	2006
0	1	1	1	1	0	0
select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006';
2000	2001	2002	2003	2004	2005	2006
1	1	1	2	2	1	1
select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006';
2000	2001	2002	2003	2004	2005	2006
199952	200053	200152	200252	200352	200452	200601
select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006';
2000	2001	2002	2003	2004	2005	2006
200001	200053	200201	200301	200401	200501	200601
select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006';
2000	2001	2002	2003	2004	2005	2006
199952	200101	200201	200301	200401	200453	200552
select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006';
2000	2001	2002	2003	2004	2005	2006
200001	200101	200201	200302	200402	200501	200601
94 95 96 97 98
select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
week(19981231,2)	week(19981231,3)	week(20000101,2)	week(20000101,3)
52	53	52	52
select week(20001231,2),week(20001231,3);
week(20001231,2)	week(20001231,3)
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
53	52
select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7';
0	1	2	3	4	5	6	7
52	53	52	53	52	52	52	52
select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7';
0	1	2	3	4	5	6	7
0	0	52	52	0	0	52	52
select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7';
0	1	2	3	4	5	6	7
1	1	1	1	1	1	1	1
select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7';
0	1	2	3	4	5	6	7
53	52	53	52	53	52	1	52
select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7';
0	1	2	3	4	5	6	7
0	1	53	1	1	1	1	1
select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7);
yearweek(20001231,0)	yearweek(20001231,1)	yearweek(20001231,2)	yearweek(20001231,3)	yearweek(20001231,4)	yearweek(20001231,5)	yearweek(20001231,6)	yearweek(20001231,7)
200053	200052	200053	200052	200101	200052	200101	200052
set default_week_format = 6;
select week(20001231), week(20001231,6);
week(20001231)	week(20001231,6)
1	1
set default_week_format = 0;
123 124 125
set default_week_format = 2;
select week(20001231),week(20001231,2),week(20001231,0);
week(20001231)	week(20001231,2)	week(20001231,0)
126
53	53	53
127
set default_week_format = 0;
128
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
129 130
date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')
1998-53	1998-53
131
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
132 133
date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')
1999-52	1999-52
134
select dayname("1962-03-03"),dayname("1962-03-03")+0;
135 136
dayname("1962-03-03")	dayname("1962-03-03")+0
Saturday	5
137
select monthname("1972-03-04"),monthname("1972-03-04")+0;
138 139
monthname("1972-03-04")	monthname("1972-03-04")+0
March	3
140
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
141 142
time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
00|12|0|12|00|AM|12:00:00 AM|00|00:00:00
143
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
144 145
time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|02|AM|01:02:03 AM|03|01:02:03
146
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
147 148
time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15
149
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
150 151
time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|00|AM|01:00:15 AM|15|01:00:15
152
select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
153 154
date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6
155
select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
156 157
date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
NULL
158
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
159 160
date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)
1998-01-01 00:00:00
161
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
162 163
date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE)
1998-01-01 00:00:59
164
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
165 166
date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR)
1998-01-01 00:59:59
167
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
168 169
date_add("1997-12-31 23:59:59",INTERVAL 1 DAY)
1998-01-01 23:59:59
170
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
171 172
date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH)
1998-01-31 23:59:59
173
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
174 175
date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR)
1998-12-31 23:59:59
176
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
177 178
date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)
1998-01-01 00:01:00
179
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
180 181
date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)
1998-01-01 01:00:59
182
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
183 184
date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR)
1998-01-02 00:59:59
185
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
186 187
date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)
1999-01-31 23:59:59
188
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
189 190
date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)
1998-01-01 01:01:00
191
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
192 193
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE)
1998-01-02 01:00:59
194
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);
195 196
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND)
1998-01-02 01:01:00
197
select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
198 199
date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)
1997-12-31 23:59:59
200
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
201 202
date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)
1997-12-31 23:59:00
203
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
204 205
date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR)
1997-12-31 23:00:00
206
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
207 208
date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)
1997-12-31 00:00:00
209
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
210 211
date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)
1997-12-01 00:00:00
212
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
213 214
date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR)
1997-01-01 00:00:00
215
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
216 217
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND)
1997-12-31 23:58:59
218
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
219 220
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE)
1997-12-31 22:59:00
221
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
222 223
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR)
1997-12-30 23:00:00
224
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
225 226
date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH)
1996-12-01 00:00:00
227
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
228 229
date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND)
1997-12-31 22:58:59
230
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
231 232
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE)
1997-12-30 22:59:00
233
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
234 235
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)
1997-12-30 22:58:59
236
select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
237 238
date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND)
1998-01-02 03:46:39
239
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
240 241
date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE)
1997-10-23 13:19:59
242
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
243 244
date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR)
2009-05-29 15:59:59
245
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
246 247
date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY)
1724-03-17 23:59:59
248
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
249 250
date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH)
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
251 252
Warnings:
Warning	1441	Datetime function: datetime field overflow
253
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
254 255
date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR)
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
256 257
Warnings:
Warning	1441	Datetime function: datetime field overflow
258
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
259 260
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND)
1998-01-07 22:40:00
261
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
262 263
date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE)
1996-11-10 07:58:59
264
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
265 266
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR)
2025-05-19 00:59:59
267
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
268 269
date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH)
1897-11-30 23:59:59
270
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
271 272
date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND)
1999-02-21 17:40:38
273
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
274 275
date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE)
1970-08-11 19:20:59
276
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
277 278
date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND)
2025-05-23 04:40:38
279
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
280 281
"1997-12-31 23:59:59" + INTERVAL 1 SECOND
1998-01-01 00:00:00
282
select INTERVAL 1 DAY + "1997-12-31";
283 284
INTERVAL 1 DAY + "1997-12-31"
1998-01-01
285
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;
286 287
"1998-01-01 00:00:00" - INTERVAL 1 SECOND
1997-12-31 23:59:59
288
select date_sub("1998-01-02",INTERVAL 31 DAY);
289 290
date_sub("1998-01-02",INTERVAL 31 DAY)
1997-12-02
291
select date_add("1997-12-31",INTERVAL 1 SECOND);
292 293
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01
294
select date_add("1997-12-31",INTERVAL 1 DAY);
295 296
date_add("1997-12-31",INTERVAL 1 DAY)
1998-01-01
297
select date_add(NULL,INTERVAL 100000 SECOND);
298 299
date_add(NULL,INTERVAL 100000 SECOND)
NULL
300
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
301 302
date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND)
NULL
303
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
304 305
date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND)
NULL
306
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
307 308
date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND)
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
309 310
Warnings:
Warning	1441	Datetime function: datetime field overflow
311
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
312 313
date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND)
NULL
314
Warnings:
315
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
316
select date_add('1998-01-30',Interval 1 month);
317 318
date_add('1998-01-30',Interval 1 month)
1998-02-28
319
select date_add('1998-01-30',Interval '2:1' year_month);
320 321
date_add('1998-01-30',Interval '2:1' year_month)
2000-02-29
322
select date_add('1996-02-29',Interval '1' year);
323 324
date_add('1996-02-29',Interval '1' year)
1997-02-28
325
select extract(YEAR FROM "1999-01-02 10:11:12");
326 327
extract(YEAR FROM "1999-01-02 10:11:12")
1999
328
select extract(YEAR_MONTH FROM "1999-01-02");
329 330
extract(YEAR_MONTH FROM "1999-01-02")
199901
331
select extract(DAY FROM "1999-01-02");
332 333
extract(DAY FROM "1999-01-02")
2
334
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
335 336
extract(DAY_HOUR FROM "1999-01-02 10:11:12")
210
337
select extract(DAY_MINUTE FROM "02 10:11:12");
338 339
extract(DAY_MINUTE FROM "02 10:11:12")
21011
340
select extract(DAY_SECOND FROM "225 10:11:12");
341 342
extract(DAY_SECOND FROM "225 10:11:12")
225101112
343
select extract(HOUR FROM "1999-01-02 10:11:12");
344 345
extract(HOUR FROM "1999-01-02 10:11:12")
10
346
select extract(HOUR_MINUTE FROM "10:11:12");
347 348
extract(HOUR_MINUTE FROM "10:11:12")
1011
349
select extract(HOUR_SECOND FROM "10:11:12");
350 351
extract(HOUR_SECOND FROM "10:11:12")
101112
352
select extract(MINUTE FROM "10:11:12");
353 354
extract(MINUTE FROM "10:11:12")
11
355
select extract(MINUTE_SECOND FROM "10:11:12");
356 357
extract(MINUTE_SECOND FROM "10:11:12")
1112
358
select extract(SECOND FROM "1999-01-02 10:11:12");
359 360
extract(SECOND FROM "1999-01-02 10:11:12")
12
361
select extract(MONTH FROM "2001-02-00");
362 363
extract(MONTH FROM "2001-02-00")
2
364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;
quarter
4
400 401 402 403 404 405
SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
9999-12-31 00:00:00
SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
9999-12-31 00:00:00
406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426
SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;
"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND
1968-01-20 03:14:08
SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND
1968-01-20 03:15:07
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND
8895-03-27 22:11:40
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
"1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND
6255-04-08 15:04:32
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
"1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE
5983-01-24 02:08:00
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR;
"1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR
2019-08-15 16:00:00
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
"1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
427 428
Warnings:
Warning	1441	Datetime function: datetime field overflow
429 430 431
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
"1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
432 433
Warnings:
Warning	1441	Datetime function: datetime field overflow
434 435 436
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
"1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
437 438
Warnings:
Warning	1441	Datetime function: datetime field overflow
439 440 441
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND
NULL
jimw@mysql.com's avatar
jimw@mysql.com committed
442 443
Warnings:
Warning	1441	Datetime function: datetime field overflow
444 445 446
create table t1 (ctime varchar(20));
insert into t1 values ('2001-01-12 12:23:40');
select ctime, hour(ctime) from t1;
tim@cane.mysql.fi's avatar
tim@cane.mysql.fi committed
447 448
ctime	hour(ctime)
2001-01-12 12:23:40	12
hf@deer.(none)'s avatar
hf@deer.(none) committed
449 450 451
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
ctime
2001-01-12 12:23:40
452 453 454 455 456 457 458 459
drop table t1;
create table t1 (id int);
create table t2 (id int, date date);
insert into t1 values (1);
insert into t2 values (1, "0000-00-00");
insert into t1 values (2);
insert into t2 values (2, "2000-01-01");
select monthname(date) from t1 inner join t2 on t1.id = t2.id;
460 461 462
monthname(date)
NULL
January
463
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
464 465 466
monthname(date)
NULL
January
467
drop table t1,t2;
468
CREATE TABLE t1 (updated text) ENGINE=MyISAM;
469 470
INSERT INTO t1 VALUES ('');
SELECT month(updated) from t1;
471 472
month(updated)
NULL
473
Warnings:
474
Warning	1292	Incorrect datetime value: ''
475
SELECT year(updated) from t1;
476 477
year(updated)
NULL
478
Warnings:
479
Warning	1292	Incorrect datetime value: ''
480
drop table t1;
481 482 483 484 485
create table t1 (d date, dt datetime, t timestamp, c char(10));
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
dayofyear("0000-00-00")	dayofyear(d)	dayofyear(dt)	dayofyear(t)	dayofyear(c)
NULL	NULL	NULL	NULL	NULL
486
Warnings:
487 488
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
489 490 491 492 493 494 495 496 497 498 499 500
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
dayofmonth("0000-00-00")	dayofmonth(d)	dayofmonth(dt)	dayofmonth(t)	dayofmonth(c)
0	0	0	0	0
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
month("0000-00-00")	month(d)	month(dt)	month(t)	month(c)
0	0	0	0	0
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
quarter("0000-00-00")	quarter(d)	quarter(dt)	quarter(t)	quarter(c)
0	0	0	0	0
select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1;
week("0000-00-00")	week(d)	week(dt)	week(t)	week(c)
NULL	NULL	NULL	NULL	NULL
501
Warnings:
502 503
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
504 505 506 507 508 509
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
year("0000-00-00")	year(d)	year(dt)	year(t)	year(c)
0	0	0	0	0
select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1;
yearweek("0000-00-00")	yearweek(d)	yearweek(dt)	yearweek(t)	yearweek(c)
NULL	NULL	NULL	NULL	NULL
510
Warnings:
511 512
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
513 514 515
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;
to_days("0000-00-00")	to_days(d)	to_days(dt)	to_days(t)	to_days(c)
NULL	NULL	NULL	NULL	NULL
516
Warnings:
517 518
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
519 520 521 522
select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
extract(MONTH FROM "0000-00-00")	extract(MONTH FROM d)	extract(MONTH FROM dt)	extract(MONTH FROM t)	extract(MONTH FROM c)
0	0	0	0	0
drop table t1;
523 524
CREATE TABLE t1 ( start datetime default NULL);
INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');
525
CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);
526 527 528 529 530
INSERT INTO t2 VALUES (20021029165106,20021105164731);
CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);
INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");
select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
start	ctime1	ctime2
531
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
532 533
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
start	ctime1	ctime2
534
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
535 536 537 538
select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
drop table t1,t2,t3;
monty@mysql.com's avatar
monty@mysql.com committed
539 540 541 542 543 544 545 546 547
select @a:=FROM_UNIXTIME(1);
@a:=FROM_UNIXTIME(1)
1970-01-01 03:00:01
select unix_timestamp(@a);
unix_timestamp(@a)
1
select unix_timestamp('1969-12-01 19:00:01');
unix_timestamp('1969-12-01 19:00:01')
0
548 549
select from_unixtime(-1);
from_unixtime(-1)
550 551 552 553
NULL
select from_unixtime(2145916800);
from_unixtime(2145916800)
NULL
554 555 556
select from_unixtime(0);
from_unixtime(0)
1970-01-01 03:00:00
monty@mysql.com's avatar
monty@mysql.com committed
557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612
CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);
INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");
SELECT * from t1;
datetime	timestamp	date	time
2001-01-02 03:04:05	2002-01-02 03:04:05	2003-01-02	06:07:08
select date_add("1997-12-31",INTERVAL 1 SECOND);
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01
select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);
date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH)
1999-01-31
select date_add(datetime, INTERVAL 1 SECOND) from t1;
date_add(datetime, INTERVAL 1 SECOND)
2001-01-02 03:04:06
select date_add(datetime, INTERVAL 1 YEAR) from t1;
date_add(datetime, INTERVAL 1 YEAR)
2002-01-02 03:04:05
select date_add(date,INTERVAL 1 SECOND) from t1;
date_add(date,INTERVAL 1 SECOND)
2003-01-02 00:00:01
select date_add(date,INTERVAL 1 MINUTE) from t1;
date_add(date,INTERVAL 1 MINUTE)
2003-01-02 00:01:00
select date_add(date,INTERVAL 1 HOUR) from t1;
date_add(date,INTERVAL 1 HOUR)
2003-01-02 01:00:00
select date_add(date,INTERVAL 1 DAY) from t1;
date_add(date,INTERVAL 1 DAY)
2003-01-03
select date_add(date,INTERVAL 1 MONTH) from t1;
date_add(date,INTERVAL 1 MONTH)
2003-02-02
select date_add(date,INTERVAL 1 YEAR) from t1;
date_add(date,INTERVAL 1 YEAR)
2004-01-02
select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;
date_add(date,INTERVAL "1:1" MINUTE_SECOND)
2003-01-02 00:01:01
select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;
date_add(date,INTERVAL "1:1" HOUR_MINUTE)
2003-01-02 01:01:00
select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;
date_add(date,INTERVAL "1:1" DAY_HOUR)
2003-01-03 01:00:00
select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;
date_add(date,INTERVAL "1 1" YEAR_MONTH)
2004-02-02
select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
date_add(date,INTERVAL "1:1:1" HOUR_SECOND)
2003-01-02 01:01:01
select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
date_add(date,INTERVAL "1 1:1" DAY_MINUTE)
2003-01-03 01:01:00
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
date_add(date,INTERVAL "1 1:1:1" DAY_SECOND)
2003-01-03 01:01:01
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669
select date_add(date,INTERVAL "1" WEEK) from t1;
date_add(date,INTERVAL "1" WEEK)
2003-01-09 00:00:00
select date_add(date,INTERVAL "1" QUARTER) from t1;
date_add(date,INTERVAL "1" QUARTER)
2003-04-02
select timestampadd(MINUTE, 1, date) from t1;
timestampadd(MINUTE, 1, date)
2003-01-02 00:01:00
select timestampadd(WEEK, 1, date) from t1;
timestampadd(WEEK, 1, date)
2003-01-09 00:00:00
select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
timestampadd(SQL_TSI_SECOND, 1, date)
2003-01-02 00:00:01
select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;
timestampadd(SQL_TSI_FRAC_SECOND, 1, date)
2003-01-02 00:00:00.000001
select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
a
3
select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
a
-1
select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
a
-5
select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
a
0
select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
a
107
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
a
12
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
a
2136
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
a
89
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
a
128159
select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
a
7689539
select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;
a
7689538999999
select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
a1	a2	a3	a4
28	28	29	29
670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')
0
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28')
2
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27')
0
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27')
0
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27')
0
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29')
2
742 743
select date_add(time,INTERVAL 1 SECOND) from t1;
date_add(time,INTERVAL 1 SECOND)
monty@mysql.com's avatar
monty@mysql.com committed
744
NULL
745
Warnings:
746
Warning	1264	Out of range value for column 'time' at row 1
747
drop table t1;
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
748 749 750 751 752 753
select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,
last_day('2003-03-32') as f3, last_day('2003-04-01') as f4,
last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
last_day('2001-02-12');
f1	f2	f3	f4	f5	last_day(NULL)	last_day('2001-02-12')
2000-02-29	2002-12-31	NULL	2003-04-30	2001-01-31	NULL	2001-02-28
754
Warnings:
755
Warning	1292	Incorrect datetime value: '2003-03-32'
756 757
create table t1 select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
758 759
describe t1;
Field	Type	Null	Key	Default	Extra
760
a	date	NO		0000-00-00	
761
b	date	YES		NULL	
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
762
select * from t1;
763 764
a	b
2000-02-29	1996-01-01
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
765
drop table t1;
766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781
select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
a	b
2000-02-29	1996-01-01
select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
date_add(last_day("1997-12-1"), INTERVAL 1 DAY)
1998-01-01
select length(last_day("1997-12-1"));
length(last_day("1997-12-1"))
10
select last_day("1997-12-1")+0;
last_day("1997-12-1")+0
19971231
select last_day("1997-12-1")+0.0;
last_day("1997-12-1")+0.0
19971231.0
782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799
select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0
1
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;
strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0
1
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0
1
select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;
strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0
1
select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0
1
select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0
1
800
explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
801 802
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
803
Warnings:
804
Note	1003	select period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname(_latin1'1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
monty@mysql.com's avatar
monty@mysql.com committed
805
SET @TMP=NOW();
806 807 808 809
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES (NOW());
INSERT INTO t1 VALUES (NOW());
INSERT INTO t1 VALUES (NOW());
monty@mysql.com's avatar
monty@mysql.com committed
810
SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
811 812 813
count(*)
3
DROP TABLE t1;
814 815 816 817
select last_day('2005-00-00');
last_day('2005-00-00')
NULL
Warnings:
818
Warning	1292	Incorrect datetime value: '2005-00-00'
819 820 821 822
select last_day('2005-00-01');
last_day('2005-00-01')
NULL
Warnings:
823
Warning	1292	Incorrect datetime value: '2005-00-01'
824 825 826 827
select last_day('2005-01-00');
last_day('2005-01-00')
NULL
Warnings:
828
Warning	1292	Incorrect datetime value: '2005-01-00'
829 830 831 832
select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
monthname(str_to_date(null, '%m'))	monthname(str_to_date(null, '%m'))	monthname(str_to_date(1, '%m'))	monthname(str_to_date(0, '%m'))
NULL	NULL	January	NULL
833 834 835 836 837 838 839
set time_zone='-6:00';
create table t1(a timestamp);
insert into t1 values (19691231190001);
select * from t1;
a
1969-12-31 19:00:01
drop table t1;
840 841 842
create table t1(f1 date, f2 time, f3 datetime);
insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
843
select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
844 845
f1
2006-01-01
846
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
847 848
f1
2006-01-01
849
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
850 851
f1
2006-01-01
852
select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
853 854
f2
12:01:02
855
select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
856 857
f2
12:01:02
858
select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
859 860
f3
2006-01-01 12:01:01
861
select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
862 863
f3
2006-01-01 12:01:01
864
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
865 866
f1
2006-01-01
867
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
868 869
f1
2006-01-01
gkodinov@dl145s.mysql.com's avatar
gkodinov@dl145s.mysql.com committed
870
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
871 872
f1
Warnings:
gkodinov@dl145s.mysql.com's avatar
gkodinov@dl145s.mysql.com committed
873 874
Warning	1292	Incorrect datetime value: 'zzz'
Warning	1292	Incorrect datetime value: 'zzz'
875 876 877 878 879 880 881
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
f1
2006-01-01
select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
f1
2006-01-02
drop table t1;
elliot@mysql.com's avatar
elliot@mysql.com committed
882
create table t1 select now() - now(), curtime() - curtime(), 
883
sec_to_time(1) + 0, from_unixtime(1) + 0;
elliot@mysql.com's avatar
elliot@mysql.com committed
884 885 886 887 888 889 890 891 892
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `now() - now()` double(23,6) NOT NULL DEFAULT '0.000000',
  `curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000',
  `sec_to_time(1) + 0` double(23,6) DEFAULT NULL,
  `from_unixtime(1) + 0` double(23,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);
H
120
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H);
H
120
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H);
H
05
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H);
H
5
913 914 915 916 917 918 919 920 921 922 923 924
SET NAMES latin1;
SET character_set_results = NULL;
SHOW VARIABLES LIKE 'character_set_results';
Variable_name	Value
character_set_results	
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);
INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');
SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;
fmtddate	field2
Sep-4 12:00AM	abcd
DROP TABLE testBug8868;
SET NAMES DEFAULT;
925
End of 4.1 tests
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
926 927
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;
928 929
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
gluh@gluh.mysql.r18.ru's avatar
gluh@gluh.mysql.r18.ru committed
930
Warnings:
931
Note	1003	select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2`
932 933 934
select time_format('100:00:00', '%H %k %h %I %l');
time_format('100:00:00', '%H %k %h %I %l')
100 100 04 04 4
935 936
create table t1 (a timestamp default '2005-05-05 01:01:01',
b timestamp default '2005-05-05 01:01:01');
937
drop function if exists t_slow_sysdate;
938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975
create function t_slow_sysdate() returns timestamp
begin
do sleep(2);
return sysdate();
end;
//
insert into t1 set a = sysdate(), b = t_slow_sysdate();//
create trigger t_before before insert on t1
for each row begin
set new.b = t_slow_sysdate();
end
//
insert into t1 set a = sysdate();
select a != b from t1;
a != b
1
1
drop trigger t_before;
drop function t_slow_sysdate;
drop table t1;
create table t1 (a datetime, i int, b datetime);
insert into t1 select sysdate(), sleep(1), sysdate() from dual;
select a != b from t1;
a != b
1
drop table t1;
create procedure t_sysdate()
begin
select sysdate() into @a;
do sleep(2);
select sysdate() into @b;
select @a != @b;
end;
//
call t_sysdate();
@a != @b
1
drop procedure t_sysdate;
976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026
select timestampdiff(month,'2004-09-11','2004-09-11');
timestampdiff(month,'2004-09-11','2004-09-11')
0
select timestampdiff(month,'2004-09-11','2005-09-11');
timestampdiff(month,'2004-09-11','2005-09-11')
12
select timestampdiff(month,'2004-09-11','2006-09-11');
timestampdiff(month,'2004-09-11','2006-09-11')
24
select timestampdiff(month,'2004-09-11','2007-09-11');
timestampdiff(month,'2004-09-11','2007-09-11')
36
select timestampdiff(month,'2005-09-11','2004-09-11');
timestampdiff(month,'2005-09-11','2004-09-11')
-12
select timestampdiff(month,'2005-09-11','2003-09-11');
timestampdiff(month,'2005-09-11','2003-09-11')
-24
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-02-29','2005-02-28');
timestampdiff(month,'2004-02-29','2005-02-28')
11
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-03-29','2005-03-28');
timestampdiff(month,'2004-03-29','2005-03-28')
11
select timestampdiff(month,'2003-02-28','2004-02-29');
timestampdiff(month,'2003-02-28','2004-02-29')
12
select timestampdiff(month,'2003-02-28','2005-02-28');
timestampdiff(month,'2003-02-28','2005-02-28')
24
select timestampdiff(month,'1999-09-11','2001-10-10');
timestampdiff(month,'1999-09-11','2001-10-10')
24
select timestampdiff(month,'1999-09-11','2001-9-11');
timestampdiff(month,'1999-09-11','2001-9-11')
24
select timestampdiff(year,'1999-09-11','2001-9-11');
timestampdiff(year,'1999-09-11','2001-9-11')
2
select timestampdiff(year,'2004-02-28','2005-02-28');
timestampdiff(year,'2004-02-28','2005-02-28')
1
select timestampdiff(year,'2004-02-29','2005-02-28');
timestampdiff(year,'2004-02-29','2005-02-28')
0
igor@rurik.mysql.com's avatar
igor@rurik.mysql.com committed
1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);
INSERT INTO t1 VALUES
(1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');
INSERT INTO t2 VALUES
(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
SELECT * FROM t1, t2 
WHERE t1.day BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
3	2005-07-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	3	2005-07-15
SELECT * FROM t1, t2 
WHERE CAST(t1.day AS DATE) BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
3	2005-07-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	3	2005-07-15
DROP TABLE t1,t2;
1052
set time_zone= @@global.time_zone;
1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087
End of 5.0 tests
select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)
NULL
select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND)
NULL
select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND)
NULL
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59
select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND)
0200-01-01 00:00:00
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59
select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)
0001-01-01 23:59:59
select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND)
2049-12-31 23:59:59
select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND)
1989-12-31 23:59:59
select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND)
NULL
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
NULL
End of 5.1 tests