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