func_time.result 35.1 KB
Newer Older
1
drop table if exists t1,t2,t3;
2
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");
3 4
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
5
select period_add("9602",-12),period_diff(199505,"9404") ;
6 7
period_add("9602",-12)	period_diff(199505,"9404")
199502	13
8
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
9
now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
10
0.000000	0	0
11
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;
12
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
1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112.000000
14 15
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);
16
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
02:30:01	23001.000000	54742	00:04:57
18 19 20
select sec_to_time(time_to_sec('-838:59:59'));
sec_to_time(time_to_sec('-838:59:59'))
-838:59:59
21
select now()-curdate()*1000000-curtime();
22
now()-curdate()*1000000-curtime()
23
0.000000
24
select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
25 26
strcmp(current_timestamp(),concat(current_date()," ",current_time()))
0
27 28 29 30 31 32
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
33
select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
34 35
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
36
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
37 38
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
39
select dayofmonth("1997-01-02"),dayofmonth(19970323);
40 41
dayofmonth("1997-01-02")	dayofmonth(19970323)
2	23
42
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
43 44
month("1997-01-02")	year("98-02-03")	dayofyear("1997-12-31")
1	1998	365
45
select month("2001-02-00"),year("2001-00-00");
46 47
month("2001-02-00")	year("2001-00-00")
2	2001
48
select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);
49 50
DAYOFYEAR("1997-03-03")	WEEK("1998-03-03")	QUARTER(980303)
62	9	1
51
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
52 53
HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
23	3	22
54
select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
55
week(19980101)	week(19970101)	week(19980101,1)	week(19970101,1)
56 57
0	0	1	1
select week(19981231),week(19971231),week(19981231,1),week(19971231,1);
58
week(19981231)	week(19971231)	week(19981231,1)	week(19971231,1)
59 60
52	52	53	53
select week(19950101),week(19950101,1);
61 62
week(19950101)	week(19950101,1)
1	0
63
select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);
64 65
yearweek('1981-12-31',1)	yearweek('1982-01-01',1)	yearweek('1982-12-31',1)	yearweek('1983-01-01',1)
198153	198153	198252	198252
66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
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
93 94 95 96 97
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)
98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
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;
122 123 124
set default_week_format = 2;
select week(20001231),week(20001231,2),week(20001231,0);
week(20001231)	week(20001231,2)	week(20001231,0)
125
53	53	53
126
set default_week_format = 0;
127
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
128 129
date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')
1998-53	1998-53
130
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
131 132
date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')
1999-52	1999-52
133
select dayname("1962-03-03"),dayname("1962-03-03")+0;
134 135
dayname("1962-03-03")	dayname("1962-03-03")+0
Saturday	5
136
select monthname("1972-03-04"),monthname("1972-03-04")+0;
137 138
monthname("1972-03-04")	monthname("1972-03-04")+0
March	3
139
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
140 141
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
142
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
143 144
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
145
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
146 147
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
148
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
149 150
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
151
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');
152 153
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
154
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');
155 156
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
157
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
158 159
date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)
1998-01-01 00:00:00
160
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
161 162
date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE)
1998-01-01 00:00:59
163
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
164 165
date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR)
1998-01-01 00:59:59
166
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
167 168
date_add("1997-12-31 23:59:59",INTERVAL 1 DAY)
1998-01-01 23:59:59
169
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
170 171
date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH)
1998-01-31 23:59:59
172
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
173 174
date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR)
1998-12-31 23:59:59
175
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
176 177
date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)
1998-01-01 00:01:00
178
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
179 180
date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)
1998-01-01 01:00:59
181
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
182 183
date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR)
1998-01-02 00:59:59
184
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
185 186
date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)
1999-01-31 23:59:59
187
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
188 189
date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)
1998-01-01 01:01:00
190
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
191 192
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE)
1998-01-02 01:00:59
193
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);
194 195
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND)
1998-01-02 01:01:00
196
select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
197 198
date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)
1997-12-31 23:59:59
199
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
200 201
date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)
1997-12-31 23:59:00
202
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
203 204
date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR)
1997-12-31 23:00:00
205
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
206 207
date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)
1997-12-31 00:00:00
208
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
209 210
date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)
1997-12-01 00:00:00
211
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
212 213
date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR)
1997-01-01 00:00:00
214
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
215 216
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND)
1997-12-31 23:58:59
217
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
218 219
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE)
1997-12-31 22:59:00
220
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
221 222
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR)
1997-12-30 23:00:00
223
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
224 225
date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH)
1996-12-01 00:00:00
226
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
227 228
date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND)
1997-12-31 22:58:59
229
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
230 231
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE)
1997-12-30 22:59:00
232
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
233 234
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)
1997-12-30 22:58:59
235
select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
236 237
date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND)
1998-01-02 03:46:39
238
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
239 240
date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE)
1997-10-23 13:19:59
241
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
242 243
date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR)
2009-05-29 15:59:59
244
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
245 246
date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY)
1724-03-17 23:59:59
247
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
248 249
date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH)
NULL
250
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
251 252
date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR)
NULL
253
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
254 255
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND)
1998-01-07 22:40:00
256
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
257 258
date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE)
1996-11-10 07:58:59
259
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
260 261
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR)
2025-05-19 00:59:59
262
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
263 264
date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH)
1897-11-30 23:59:59
265
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
266 267
date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND)
1999-02-21 17:40:38
268
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
269 270
date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE)
1970-08-11 19:20:59
271
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
272 273
date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND)
2025-05-23 04:40:38
274
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
275 276
"1997-12-31 23:59:59" + INTERVAL 1 SECOND
1998-01-01 00:00:00
277
select INTERVAL 1 DAY + "1997-12-31";
278 279
INTERVAL 1 DAY + "1997-12-31"
1998-01-01
280
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;
281 282
"1998-01-01 00:00:00" - INTERVAL 1 SECOND
1997-12-31 23:59:59
283
select date_sub("1998-01-02",INTERVAL 31 DAY);
284 285
date_sub("1998-01-02",INTERVAL 31 DAY)
1997-12-02
286
select date_add("1997-12-31",INTERVAL 1 SECOND);
287 288
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01
289
select date_add("1997-12-31",INTERVAL 1 DAY);
290 291
date_add("1997-12-31",INTERVAL 1 DAY)
1998-01-01
292
select date_add(NULL,INTERVAL 100000 SECOND);
293 294
date_add(NULL,INTERVAL 100000 SECOND)
NULL
295
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
296 297
date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND)
NULL
298
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
299 300
date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND)
NULL
301
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
302 303
date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND)
NULL
304
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
305 306
date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND)
NULL
307
select date_add('1998-01-30',Interval 1 month);
308 309
date_add('1998-01-30',Interval 1 month)
1998-02-28
310
select date_add('1998-01-30',Interval '2:1' year_month);
311 312
date_add('1998-01-30',Interval '2:1' year_month)
2000-02-29
313
select date_add('1996-02-29',Interval '1' year);
314 315
date_add('1996-02-29',Interval '1' year)
1997-02-28
316
select extract(YEAR FROM "1999-01-02 10:11:12");
317 318
extract(YEAR FROM "1999-01-02 10:11:12")
1999
319
select extract(YEAR_MONTH FROM "1999-01-02");
320 321
extract(YEAR_MONTH FROM "1999-01-02")
199901
322
select extract(DAY FROM "1999-01-02");
323 324
extract(DAY FROM "1999-01-02")
2
325
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
326 327
extract(DAY_HOUR FROM "1999-01-02 10:11:12")
210
328
select extract(DAY_MINUTE FROM "02 10:11:12");
329 330
extract(DAY_MINUTE FROM "02 10:11:12")
21011
331
select extract(DAY_SECOND FROM "225 10:11:12");
332 333
extract(DAY_SECOND FROM "225 10:11:12")
225101112
334
select extract(HOUR FROM "1999-01-02 10:11:12");
335 336
extract(HOUR FROM "1999-01-02 10:11:12")
10
337
select extract(HOUR_MINUTE FROM "10:11:12");
338 339
extract(HOUR_MINUTE FROM "10:11:12")
1011
340
select extract(HOUR_SECOND FROM "10:11:12");
341 342
extract(HOUR_SECOND FROM "10:11:12")
101112
343
select extract(MINUTE FROM "10:11:12");
344 345
extract(MINUTE FROM "10:11:12")
11
346
select extract(MINUTE_SECOND FROM "10:11:12");
347 348
extract(MINUTE_SECOND FROM "10:11:12")
1112
349
select extract(SECOND FROM "1999-01-02 10:11:12");
350 351
extract(SECOND FROM "1999-01-02 10:11:12")
12
352
select extract(MONTH FROM "2001-02-00");
353 354
extract(MONTH FROM "2001-02-00")
2
355 356 357 358 359 360
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
361 362 363 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
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
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
"1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE
NULL
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
"1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR
NULL
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND
NULL
391 392 393
create table t1 (ctime varchar(20));
insert into t1 values ('2001-01-12 12:23:40');
select ctime, hour(ctime) from t1;
unknown's avatar
unknown committed
394 395
ctime	hour(ctime)
2001-01-12 12:23:40	12
unknown's avatar
unknown committed
396 397 398
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
ctime
2001-01-12 12:23:40
399 400 401 402 403 404 405 406
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;
407 408 409
monthname(date)
NULL
January
410
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
411 412 413
monthname(date)
NULL
January
414
drop table t1,t2;
unknown's avatar
unknown committed
415
CREATE TABLE t1 (updated text) ENGINE=MyISAM;
416 417
INSERT INTO t1 VALUES ('');
SELECT month(updated) from t1;
unknown's avatar
unknown committed
418 419
month(updated)
NULL
420 421
Warnings:
Warning	1292	Truncated incorrect datetime value: ''
422
SELECT year(updated) from t1;
unknown's avatar
unknown committed
423 424
year(updated)
NULL
425 426
Warnings:
Warning	1292	Truncated incorrect datetime value: ''
427
drop table t1;
428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457
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
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
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
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
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;
458 459 460 461 462 463 464 465
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');
CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL);
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
466
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
467 468
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
start	ctime1	ctime2
469
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
470 471 472 473
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;
unknown's avatar
unknown committed
474 475 476 477 478 479 480 481 482
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
483 484
select from_unixtime(-1);
from_unixtime(-1)
485
NULL
486 487 488 489 490
select from_unixtime(2147483647);
from_unixtime(2147483647)
2038-01-19 06:14:07
select from_unixtime(2147483648);
from_unixtime(2147483648)
491
NULL
492 493 494
select from_unixtime(0);
from_unixtime(0)
1970-01-01 03:00:00
495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527
select unix_timestamp(from_unixtime(2147483647));
unix_timestamp(from_unixtime(2147483647))
2147483647
select unix_timestamp(from_unixtime(2147483648));
unix_timestamp(from_unixtime(2147483648))
NULL
select unix_timestamp('2039-01-20 01:00:00');
unix_timestamp('2039-01-20 01:00:00')
0
select unix_timestamp('1968-01-20 01:00:00');
unix_timestamp('1968-01-20 01:00:00')
0
select unix_timestamp('2038-02-10 01:00:00');
unix_timestamp('2038-02-10 01:00:00')
0
select unix_timestamp('1969-11-20 01:00:00');
unix_timestamp('1969-11-20 01:00:00')
0
select unix_timestamp('2038-01-20 01:00:00');
unix_timestamp('2038-01-20 01:00:00')
0
select unix_timestamp('1969-12-30 01:00:00');
unix_timestamp('1969-12-30 01:00:00')
0
select unix_timestamp('2038-01-17 12:00:00');
unix_timestamp('2038-01-17 12:00:00')
2147331600
select unix_timestamp('1970-01-01 03:00:01');
unix_timestamp('1970-01-01 03:00:01')
1
select unix_timestamp('2038-01-19 07:14:07');
unix_timestamp('2038-01-19 07:14:07')
0
unknown's avatar
unknown committed
528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 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
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
select date_add(time,INTERVAL 1 SECOND) from t1;
date_add(time,INTERVAL 1 SECOND)
unknown's avatar
unknown committed
586
NULL
587 588
Warnings:
Warning	1264	Data truncated; out of range for column 'time' at row 1
unknown's avatar
unknown committed
589 590 591 592 593 594 595
drop table t1;
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
596 597
Warnings:
Warning	1292	Truncated incorrect datetime value: '2003-03-32'
598 599
create table t1 select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
unknown's avatar
unknown committed
600 601 602
describe t1;
Field	Type	Null	Key	Default	Extra
a	date			0000-00-00	
603
b	date	YES		NULL	
unknown's avatar
unknown committed
604
select * from t1;
605 606
a	b
2000-02-29	1996-01-01
unknown's avatar
unknown committed
607
drop table t1;
608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623
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
unknown's avatar
unknown committed
624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645
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
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);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
unknown's avatar
unknown committed
646
Note	1003	select sql_no_cache 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(to_days(curdate())) - weekday(to_days(now()))) AS `weekday(curdate())-weekday(now())`,dayname(to_days(_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)`
unknown's avatar
unknown committed
647
SET @TMP=NOW();
648 649 650 651
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES (NOW());
INSERT INTO t1 VALUES (NOW());
INSERT INTO t1 VALUES (NOW());
unknown's avatar
unknown committed
652
SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
653 654 655
count(*)
3
DROP TABLE t1;
656 657 658 659 660 661 662 663 664 665 666 667 668 669 670
select last_day('2005-00-00');
last_day('2005-00-00')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-00-00'
select last_day('2005-00-01');
last_day('2005-00-01')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-00-01'
select last_day('2005-01-00');
last_day('2005-01-00')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-01-00'
671 672 673 674
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
675 676 677 678 679 680 681
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;
682 683 684
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");
685
select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
686 687
f1
2006-01-01
688
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
689 690
f1
2006-01-01
691
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
692 693
f1
2006-01-01
694
select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
695 696
f2
12:01:02
697
select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
698 699
f2
12:01:02
700
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);
701 702
f3
2006-01-01 12:01:01
703
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);
704 705
f3
2006-01-01 12:01:01
706
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
707 708
f1
2006-01-01
709
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
710 711
f1
2006-01-01
712
select f1 from t1 where cast("2006-1-1" as date) between f1 and 'zzz';
713
f1
714
2006-01-01
715 716 717 718 719 720 721
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;
722
create table t1 select now() - now(), curtime() - curtime(), 
723
sec_to_time(1) + 0, from_unixtime(1) + 0;
724 725 726 727 728 729 730 731 732
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;
733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753
(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
End of 4.1 tests