← Back to branch summary

~drizzle-trunk/drizzle/development

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 date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");

15
 
date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")

16
 
January Thursday 2nd 1997 97 01 02 03 04 05 4

17
 
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));

18
 
date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"))

19
 
January Thursday 2nd 1997 97 01 02 12 00 00 4

20
 
select dayofmonth("1997-01-02"),dayofmonth(19970323);

21
 
dayofmonth("1997-01-02")	dayofmonth(19970323)

22
 
2	23

23
 
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");

24
 
month("1997-01-02")	year("98-02-03")	dayofyear("1997-12-31")

25
 
1	1998	365
813.1.2 by Jay Pipes
First function cleanup for temporal handling: YEAR() 
26
 
select month("2001-02-00"),year("2001-01-01");
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
27
 
ERROR HY000: Received an invalid datetime value '2001-02-00'.
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
28
 
select DAYOFYEAR("1997-03-03"), QUARTER(980303);

29
 
DAYOFYEAR("1997-03-03")	QUARTER(980303)

30
 
62	1
1 by brian
clean slate 
31
 
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);

32
 
HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)

33
 
23	3	22

34
 
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');

35
 
date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')

36
 
1998-53	1998-53

37
 
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');

38
 
date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')

39
 
1999-52	1999-52

40
 
select dayname("1962-03-03"),dayname("1962-03-03")+0;

41
 
dayname("1962-03-03")	dayname("1962-03-03")+0

42
 
Saturday	5

43
 
select monthname("1972-03-04"),monthname("1972-03-04")+0;

44
 
monthname("1972-03-04")	monthname("1972-03-04")+0

45
 
March	3

46
 
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

47
 
time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')

48
 
00|12|0|12|00|AM|12:00:00 AM|00|00:00:00

49
 
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

50
 
time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T')

51
 
01|01|1|1|02|AM|01:02:03 AM|03|01:02:03

52
 
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

53
 
time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T')

54
 
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15

55
 
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

56
 
time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T')

57
 
01|01|1|1|00|AM|01:00:15 AM|15|01:00:15

58
 
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');

59
 
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')

60
 
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

61
 
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');

62
 
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')

63
 
NULL

64
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

65
 
date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)

66
 
1998-01-01 00:00:00

67
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);

68
 
date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE)

69
 
1998-01-01 00:00:59

70
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);

71
 
date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR)

72
 
1998-01-01 00:59:59

73
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);

74
 
date_add("1997-12-31 23:59:59",INTERVAL 1 DAY)

75
 
1998-01-01 23:59:59

76
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);

77
 
date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH)

78
 
1998-01-31 23:59:59

79
 
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);

80
 
date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR)

81
 
1998-12-31 23:59:59

82
 
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);

83
 
date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)

84
 
1998-01-01 00:01:00

85
 
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);

86
 
date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)

87
 
1998-01-01 01:00:59

88
 
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);

89
 
date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR)

90
 
1998-01-02 00:59:59

91
 
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);

92
 
date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)

93
 
1999-01-31 23:59:59

94
 
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);

95
 
date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)

96
 
1998-01-01 01:01:00

97
 
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);

98
 
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE)

99
 
1998-01-02 01:00:59

100
 
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);

101
 
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND)

102
 
1998-01-02 01:01:00

103
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);

104
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)

105
 
1997-12-31 23:59:59

106
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);

107
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)

108
 
1997-12-31 23:59:00

109
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);

110
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR)

111
 
1997-12-31 23:00:00

112
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);

113
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)

114
 
1997-12-31 00:00:00

115
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);

116
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)

117
 
1997-12-01 00:00:00

118
 
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);

119
 
date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR)

120
 
1997-01-01 00:00:00

121
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);

122
 
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND)

123
 
1997-12-31 23:58:59

124
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);

125
 
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE)

126
 
1997-12-31 22:59:00

127
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);

128
 
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR)

129
 
1997-12-30 23:00:00

130
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);

131
 
date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH)

132
 
1996-12-01 00:00:00

133
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);

134
 
date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND)

135
 
1997-12-31 22:58:59

136
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);

137
 
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE)

138
 
1997-12-30 22:59:00

139
 
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);

140
 
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)

141
 
1997-12-30 22:58:59

142
 
select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);

143
 
date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND)

144
 
1998-01-02 03:46:39

145
 
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);

146
 
date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE)

147
 
1997-10-23 13:19:59

148
 
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);

149
 
date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR)

150
 
2009-05-29 15:59:59

151
 
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);

152
 
date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY)

153
 
1724-03-17 23:59:59

154
 
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);

155
 
date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH)

156
 
NULL

157
 
Warnings:

158
 
Warning	1441	Datetime function: datetime field overflow

159
 
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);

160
 
date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR)

161
 
NULL

162
 
Warnings:

163
 
Warning	1441	Datetime function: datetime field overflow

164
 
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);

165
 
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND)

166
 
1998-01-07 22:40:00

167
 
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);

168
 
date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE)

169
 
1996-11-10 07:58:59

170
 
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);

171
 
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR)

172
 
2025-05-19 00:59:59

173
 
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);

174
 
date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH)

175
 
1897-11-30 23:59:59

176
 
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);

177
 
date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND)

178
 
1999-02-21 17:40:38

179
 
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);

180
 
date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE)

181
 
1970-08-11 19:20:59

182
 
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);

183
 
date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND)

184
 
2025-05-23 04:40:38

185
 
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

186
 
"1997-12-31 23:59:59" + INTERVAL 1 SECOND

187
 
1998-01-01 00:00:00

188
 
select INTERVAL 1 DAY + "1997-12-31";

189
 
INTERVAL 1 DAY + "1997-12-31"

190
 
1998-01-01

191
 
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;

192
 
"1998-01-01 00:00:00" - INTERVAL 1 SECOND

193
 
1997-12-31 23:59:59

194
 
select date_sub("1998-01-02",INTERVAL 31 DAY);

195
 
date_sub("1998-01-02",INTERVAL 31 DAY)

196
 
1997-12-02

197
 
select date_add("1997-12-31",INTERVAL 1 SECOND);

198
 
date_add("1997-12-31",INTERVAL 1 SECOND)

199
 
1997-12-31 00:00:01

200
 
select date_add("1997-12-31",INTERVAL 1 DAY);

201
 
date_add("1997-12-31",INTERVAL 1 DAY)

202
 
1998-01-01

203
 
select date_add(NULL,INTERVAL 100000 SECOND);

204
 
date_add(NULL,INTERVAL 100000 SECOND)

205
 
NULL

206
 
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);

207
 
date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND)

208
 
NULL

209
 
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);

210
 
date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND)

211
 
NULL

212
 
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);

213
 
date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND)

214
 
NULL

215
 
Warnings:

216
 
Warning	1441	Datetime function: datetime field overflow

217
 
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);

218
 
date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND)

219
 
NULL

220
 
Warnings:

221
 
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'

222
 
select date_add('1998-01-30',Interval 1 month);

223
 
date_add('1998-01-30',Interval 1 month)

224
 
1998-02-28

225
 
select date_add('1998-01-30',Interval '2:1' year_month);

226
 
date_add('1998-01-30',Interval '2:1' year_month)

227
 
2000-02-29

228
 
select date_add('1996-02-29',Interval '1' year);

229
 
date_add('1996-02-29',Interval '1' year)

230
 
1997-02-28

231
 
select extract(YEAR FROM "1999-01-02 10:11:12");

232
 
extract(YEAR FROM "1999-01-02 10:11:12")

233
 
1999

234
 
select extract(YEAR_MONTH FROM "1999-01-02");

235
 
extract(YEAR_MONTH FROM "1999-01-02")

236
 
199901

237
 
select extract(DAY FROM "1999-01-02");

238
 
extract(DAY FROM "1999-01-02")

239
 
2

240
 
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");

241
 
extract(DAY_HOUR FROM "1999-01-02 10:11:12")

242
 
210

243
 
select extract(DAY_MINUTE FROM "02 10:11:12");
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
244
 
ERROR HY000: Received an invalid datetime value '02 10:11:12'.
1 by brian
clean slate 
245
 
select extract(DAY_SECOND FROM "225 10:11:12");
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
246
 
ERROR HY000: Received an invalid datetime value '225 10:11:12'.
1 by brian
clean slate 
247
 
select extract(HOUR FROM "1999-01-02 10:11:12");

248
 
extract(HOUR FROM "1999-01-02 10:11:12")

249
 
10

250
 
select extract(HOUR_MINUTE FROM "10:11:12");

251
 
extract(HOUR_MINUTE FROM "10:11:12")

252
 
1011

253
 
select extract(HOUR_SECOND FROM "10:11:12");

254
 
extract(HOUR_SECOND FROM "10:11:12")

255
 
101112

256
 
select extract(MINUTE FROM "10:11:12");

257
 
extract(MINUTE FROM "10:11:12")

258
 
11

259
 
select extract(MINUTE_SECOND FROM "10:11:12");

260
 
extract(MINUTE_SECOND FROM "10:11:12")

261
 
1112

262
 
select extract(SECOND FROM "1999-01-02 10:11:12");

263
 
extract(SECOND FROM "1999-01-02 10:11:12")

264
 
12

265
 
select extract(MONTH FROM "2001-02-00");
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
266
 
ERROR HY000: Received an invalid datetime value '2001-02-00'.
1 by brian
clean slate 
267
 
SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;

268
 
quarter

269
 
1

270
 
SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;

271
 
quarter

272
 
1

273
 
SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;

274
 
quarter

275
 
1

276
 
SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;

277
 
quarter

278
 
2

279
 
SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;

280
 
quarter

281
 
2

282
 
SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;

283
 
quarter

284
 
2

285
 
SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;

286
 
quarter

287
 
3

288
 
SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;

289
 
quarter

290
 
3

291
 
SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;

292
 
quarter

293
 
3

294
 
SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;

295
 
quarter

296
 
4

297
 
SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;

298
 
quarter

299
 
4

300
 
SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;

301
 
quarter

302
 
4

303
 
SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;

304
 
"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND

305
 
1968-01-20 03:14:08

306
 
SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;

307
 
"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND

308
 
1968-01-20 03:15:07

309
 
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;

310
 
"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND

311
 
8895-03-27 22:11:40

312
 
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;

313
 
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND

314
 
NULL

315
 
Warnings:

316
 
Warning	1441	Datetime function: datetime field overflow

317
 
create table t1 (ctime varchar(20));

318
 
insert into t1 values ('2001-01-12 12:23:40');

319
 
select ctime, hour(ctime) from t1;

320
 
ctime	hour(ctime)

321
 
2001-01-12 12:23:40	12

322
 
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;

323
 
ctime

324
 
2001-01-12 12:23:40

325
 
drop table t1;

326
 
create table t1 (id int);

327
 
create table t2 (id int, date date);

328
 
insert into t1 values (1);
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
329
 
insert into t2 values (1, NULL);
1 by brian
clean slate 
330
 
insert into t1 values (2);

331
 
insert into t2 values (2, "2000-01-01");

332
 
select monthname(date) from t1 inner join t2 on t1.id = t2.id;

333
 
monthname(date)

334
 
NULL
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
335
 
NULL
1 by brian
clean slate 
336
 
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;

337
 
monthname(date)

338
 
NULL
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
339
 
NULL
1 by brian
clean slate 
340
 
drop table t1,t2;

341
 
CREATE TABLE t1 (updated text) ENGINE=MyISAM;

342
 
INSERT INTO t1 VALUES ('');

343
 
SELECT month(updated) from t1;
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
344
 
ERROR HY000: Received an invalid datetime value ''.
1 by brian
clean slate 
345
 
SELECT year(updated) from t1;
813.1.2 by Jay Pipes
First function cleanup for temporal handling: YEAR() 
346
 
ERROR HY000: Received an invalid datetime value ''.
1 by brian
clean slate 
347
 
drop table t1;

348
 
create table t1 (d date, dt datetime, t timestamp, c char(10));
873.1.1 by Jay Pipes
Fixes the Field_date class to not allow any invalid input at 
349
 
insert into t1 values (null, null, null, null);
1 by brian
clean slate 
350
 
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
873.1.1 by Jay Pipes
Fixes the Field_date class to not allow any invalid input at 
351
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
352
 
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
813.1.11 by Jay Pipes
Fixed DAYOFYEAR() to use new Temporal system, corrected func_time.test 
353
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
354
 
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
813.1.10 by Jay Pipes
Fixes DAYOFMONTH() function to use new Temporal system. Now throws 
355
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
356
 
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
813.1.9 by Jay Pipes
Fixes to existing func_time.test for bad datetimes passed to MONTH() and MONTHNAME() function. 
357
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
358
 
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
813.1.20 by Jay Pipes
Fixes for the QUARTER() function to use new Temporal system and throw 
359
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
360
 
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
813.1.2 by Jay Pipes
First function cleanup for temporal handling: YEAR() 
361
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
873.1.7 by Jay Pipes
Fixes Field_datetime::store(NUMBER) to throw an error when invalid 
362
 
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(c) from t1;
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
363
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
364
 
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;
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
365
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
366
 
drop table t1;

367
 
CREATE TABLE t1 ( start datetime default NULL);

368
 
INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');

369
 
CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);

370
 
INSERT INTO t2 VALUES (20021029165106,20021105164731);

371
 
CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);

372
 
INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");

373
 
select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;

374
 
start	ctime1	ctime2

375
 
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31

376
 
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;

377
 
start	ctime1	ctime2

378
 
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31

379
 
select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;

380
 
start	ctime1	ctime2

381
 
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31

382
 
drop table t1,t2,t3;

383
 
select @a:=FROM_UNIXTIME(1);

384
 
@a:=FROM_UNIXTIME(1)

385
 
1970-01-01 03:00:01

386
 
select unix_timestamp(@a);

387
 
unix_timestamp(@a)

388
 
1

389
 
select unix_timestamp('1969-12-01 19:00:01');

390
 
unix_timestamp('1969-12-01 19:00:01')

391
 
0

392
 
select from_unixtime(-1);

393
 
from_unixtime(-1)

394
 
NULL

395
 
select from_unixtime(2147483647);

396
 
from_unixtime(2147483647)

397
 
2038-01-19 06:14:07

398
 
select from_unixtime(2147483648);

399
 
from_unixtime(2147483648)

400
 
NULL

401
 
select from_unixtime(0);

402
 
from_unixtime(0)

403
 
1970-01-01 03:00:00

404
 
select unix_timestamp(from_unixtime(2147483647));

405
 
unix_timestamp(from_unixtime(2147483647))

406
 
2147483647

407
 
select unix_timestamp(from_unixtime(2147483648));

408
 
unix_timestamp(from_unixtime(2147483648))

409
 
NULL

410
 
select unix_timestamp('2039-01-20 01:00:00');

411
 
unix_timestamp('2039-01-20 01:00:00')

412
 
0

413
 
select unix_timestamp('1968-01-20 01:00:00');

414
 
unix_timestamp('1968-01-20 01:00:00')

415
 
0

416
 
select unix_timestamp('2038-02-10 01:00:00');

417
 
unix_timestamp('2038-02-10 01:00:00')

418
 
0

419
 
select unix_timestamp('1969-11-20 01:00:00');

420
 
unix_timestamp('1969-11-20 01:00:00')

421
 
0

422
 
select unix_timestamp('2038-01-20 01:00:00');

423
 
unix_timestamp('2038-01-20 01:00:00')

424
 
0

425
 
select unix_timestamp('1969-12-30 01:00:00');

426
 
unix_timestamp('1969-12-30 01:00:00')

427
 
0

428
 
select unix_timestamp('2038-01-17 12:00:00');

429
 
unix_timestamp('2038-01-17 12:00:00')

430
 
2147331600

431
 
select unix_timestamp('1970-01-01 03:00:01');

432
 
unix_timestamp('1970-01-01 03:00:01')

433
 
1

434
 
select unix_timestamp('2038-01-19 07:14:07');

435
 
unix_timestamp('2038-01-19 07:14:07')

436
 
0
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
437
 
CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date);

438
 
INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02");
1 by brian
clean slate 
439
 
SELECT * from t1;
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
440
 
datetime	timestamp	date

441
 
2001-01-02 03:04:05	2002-01-02 03:04:05	2003-01-02
1 by brian
clean slate 
442
 
select date_add("1997-12-31",INTERVAL 1 SECOND);

443
 
date_add("1997-12-31",INTERVAL 1 SECOND)

444
 
1997-12-31 00:00:01

445
 
select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);

446
 
date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH)

447
 
1999-01-31

448
 
select date_add(datetime, INTERVAL 1 SECOND) from t1;

449
 
date_add(datetime, INTERVAL 1 SECOND)

450
 
2001-01-02 03:04:06

451
 
select date_add(datetime, INTERVAL 1 YEAR) from t1;

452
 
date_add(datetime, INTERVAL 1 YEAR)

453
 
2002-01-02 03:04:05

454
 
select date_add(date,INTERVAL 1 SECOND) from t1;

455
 
date_add(date,INTERVAL 1 SECOND)

456
 
2003-01-02 00:00:01

457
 
select date_add(date,INTERVAL 1 MINUTE) from t1;

458
 
date_add(date,INTERVAL 1 MINUTE)

459
 
2003-01-02 00:01:00

460
 
select date_add(date,INTERVAL 1 HOUR) from t1;

461
 
date_add(date,INTERVAL 1 HOUR)

462
 
2003-01-02 01:00:00

463
 
select date_add(date,INTERVAL 1 DAY) from t1;

464
 
date_add(date,INTERVAL 1 DAY)

465
 
2003-01-03

466
 
select date_add(date,INTERVAL 1 MONTH) from t1;

467
 
date_add(date,INTERVAL 1 MONTH)

468
 
2003-02-02

469
 
select date_add(date,INTERVAL 1 YEAR) from t1;

470
 
date_add(date,INTERVAL 1 YEAR)

471
 
2004-01-02

472
 
select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;

473
 
date_add(date,INTERVAL "1:1" MINUTE_SECOND)

474
 
2003-01-02 00:01:01

475
 
select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;

476
 
date_add(date,INTERVAL "1:1" HOUR_MINUTE)

477
 
2003-01-02 01:01:00

478
 
select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;

479
 
date_add(date,INTERVAL "1:1" DAY_HOUR)

480
 
2003-01-03 01:00:00

481
 
select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;

482
 
date_add(date,INTERVAL "1 1" YEAR_MONTH)

483
 
2004-02-02

484
 
select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;

485
 
date_add(date,INTERVAL "1:1:1" HOUR_SECOND)

486
 
2003-01-02 01:01:01

487
 
select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;

488
 
date_add(date,INTERVAL "1 1:1" DAY_MINUTE)

489
 
2003-01-03 01:01:00

490
 
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;

491
 
date_add(date,INTERVAL "1 1:1:1" DAY_SECOND)

492
 
2003-01-03 01:01:01

493
 
select date_add(date,INTERVAL "1" WEEK) from t1;

494
 
date_add(date,INTERVAL "1" WEEK)

495
 
2003-01-09

496
 
select date_add(date,INTERVAL "1" QUARTER) from t1;

497
 
date_add(date,INTERVAL "1" QUARTER)

498
 
2003-04-02

499
 
select timestampadd(MINUTE, 1, date) from t1;

500
 
timestampadd(MINUTE, 1, date)

501
 
2003-01-02 00:01:00

502
 
select timestampadd(WEEK, 1, date) from t1;

503
 
timestampadd(WEEK, 1, date)

504
 
2003-01-09

505
 
select timestampadd(SQL_TSI_SECOND, 1, date) from t1;

506
 
timestampadd(SQL_TSI_SECOND, 1, date)

507
 
2003-01-02 00:00:01

508
 
select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;

509
 
timestampadd(SQL_TSI_FRAC_SECOND, 1, date)

510
 
2003-01-02 00:00:00.000001

511
 
select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;

512
 
a

513
 
3

514
 
select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;

515
 
a

516
 
-1

517
 
select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;

518
 
a

519
 
-5

520
 
select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;

521
 
a

522
 
0

523
 
select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;

524
 
a

525
 
107

526
 
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;

527
 
a

528
 
12

529
 
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;

530
 
a

531
 
2136

532
 
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;

533
 
a

534
 
89

535
 
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;

536
 
a

537
 
128159

538
 
select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;

539
 
a

540
 
7689539

541
 
select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;

542
 
a

543
 
7689538999999

544
 
select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,

545
 
timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,

546
 
timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,

547
 
timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;

548
 
a1	a2	a3	a4

549
 
28	28	29	29

550
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');

551
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')

552
 
0

553
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');

554
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28')

555
 
1

556
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');

557
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29')

558
 
1

559
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');

560
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27')

561
 
1

562
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');

563
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28')

564
 
2

565
 
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');

566
 
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29')

567
 
2

568
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');

569
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27')

570
 
0

571
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');

572
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28')

573
 
1

574
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');

575
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29')

576
 
1

577
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');

578
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27')

579
 
1

580
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');

581
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28')

582
 
2

583
 
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');

584
 
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29')

585
 
2

586
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');

587
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27')

588
 
0

589
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');

590
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28')

591
 
1

592
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');

593
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29')

594
 
1

595
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');

596
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27')

597
 
1

598
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');

599
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28')

600
 
2

601
 
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');

602
 
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29')

603
 
2

604
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');

605
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27')

606
 
0

607
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');

608
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28')

609
 
1

610
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');

611
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29')

612
 
1

613
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');

614
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27')

615
 
1

616
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');

617
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28')

618
 
2

619
 
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');

620
 
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29')

621
 
2

622
 
drop table t1;
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
623
 
select last_day('2000-02-05');

624
 
last_day('2000-02-05')

625
 
2000-02-29

626
 
select last_day('2002-12-31');

627
 
last_day('2002-12-31')

628
 
2002-12-31

629
 
select last_day('2003-03-32');

630
 
ERROR HY000: Received an invalid datetime value '2003-03-32'.

631
 
select last_day('2003-04-01');

632
 
last_day('2003-04-01')

633
 
2003-04-30

634
 
select last_day('2001-01-01 01:01:01');

635
 
last_day('2001-01-01 01:01:01')

636
 
2001-01-31

637
 
select last_day(NULL);

638
 
last_day(NULL)

639
 
NULL

640
 
select last_day('2001-02-12');

641
 
last_day('2001-02-12')

642
 
2001-02-28
1 by brian
clean slate 
643
 
create table t1 select last_day('2000-02-05') as a,

644
 
from_days(to_days("960101")) as b;

645
 
describe t1;

646
 
Field	Type	Null	Key	Default	Extra
896.3.6 by Stewart Smith
Read Fields out of proto instead of FRM. 
647
 
a	date	NO		NULL
1 by brian
clean slate 
648
 
b	date	YES		NULL	

649
 
select * from t1;

650
 
a	b

651
 
2000-02-29	1996-01-01

652
 
drop table t1;

653
 
select last_day('2000-02-05') as a,

654
 
from_days(to_days("960101")) as b;

655
 
a	b

656
 
2000-02-29	1996-01-01

657
 
select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);

658
 
date_add(last_day("1997-12-1"), INTERVAL 1 DAY)

659
 
1998-01-01

660
 
select length(last_day("1997-12-1"));

661
 
length(last_day("1997-12-1"))

662
 
10

663
 
select last_day("1997-12-1")+0;

664
 
last_day("1997-12-1")+0

665
 
19971231

666
 
select last_day("1997-12-1")+0.0;

667
 
last_day("1997-12-1")+0.0

668
 
19971231.0

669
 
select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;

670
 
strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0

671
 
1

672
 
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;

673
 
strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0

674
 
1

675
 
select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;

676
 
strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0

677
 
1
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
678
 
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),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),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);
1 by brian
clean slate 
679
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

680
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

681
 
Warnings:
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
682
 
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)`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,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)`
1 by brian
clean slate 
683
 
SET @TMP='2007-08-01 12:22:49';

684
 
CREATE TABLE t1 (d DATETIME);

685
 
INSERT INTO t1 VALUES ('2007-08-01 12:22:59');

686
 
INSERT INTO t1 VALUES ('2007-08-01 12:23:01');

687
 
INSERT INTO t1 VALUES ('2007-08-01 12:23:20');

688
 
SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);

689
 
count(*)

690
 
3

691
 
DROP TABLE t1;

692
 
select last_day('2005-00-00');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
693
 
ERROR HY000: Received an invalid datetime value '2005-00-00'.
1 by brian
clean slate 
694
 
select last_day('2005-00-01');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
695
 
ERROR HY000: Received an invalid datetime value '2005-00-01'.
1 by brian
clean slate 
696
 
select last_day('2005-01-00');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
697
 
ERROR HY000: Received an invalid datetime value '2005-01-00'.
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
698
 
create table t1(f1 date, f3 datetime);

699
 
insert into t1 values ("2006-01-01", "2006-01-01 12:01:01");

700
 
insert into t1 values ("2006-01-02", "2006-01-02 12:01:02");
1 by brian
clean slate 
701
 
select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);

702
 
f1

703
 
2006-01-01

704
 
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);

705
 
f1

706
 
2006-01-01

707
 
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);

708
 
f1

709
 
2006-01-01

710
 
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);

711
 
f3

712
 
2006-01-01 12:01:01

713
 
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);

714
 
f3

715
 
2006-01-01 12:01:01

716
 
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;

717
 
f1

718
 
2006-01-01

719
 
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);

720
 
f1

721
 
2006-01-01

722
 
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);

723
 
f1

724
 
Warnings:

725
 
Warning	1292	Incorrect datetime value: 'zzz'

726
 
Warning	1292	Incorrect datetime value: 'zzz'

727
 
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);

728
 
f1

729
 
2006-01-01

730
 
select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);

731
 
f1

732
 
2006-01-02

733
 
drop table t1;

734
 
SELECT EXTRACT(HOUR FROM '100000:02:03');
813.1.24 by Jay Pipes
fixed logic error in EXTRACT() function whereby datetime temporals were improperly pointing to the time temporals. Fixed test cases for func_time and func_sapdb to remove the week and weekofyear functions. 
735
 
ERROR HY000: Received an invalid datetime value '100000:02:03'.
1 by brian
clean slate 
736
 
SHOW VARIABLES LIKE 'character_set_results';

737
 
Variable_name	Value
506 by Brian Aker
Added back more tests. 
738
 
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));
1 by brian
clean slate 
739
 
INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');

740
 
SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;

741
 
fmtddate	field2

742
 
Sep-4 12:00AM	abcd

743
 
DROP TABLE testBug8868;

744
 
select last_day('0000-00-00');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
745
 
ERROR HY000: Received an invalid datetime value '0000-00-00'.
1 by brian
clean slate 
746
 
End of 4.1 tests

747
 
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,

748
 
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;

749
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

750
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

751
 
Warnings:

752
 
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`

753
 
select time_format('100:00:00', '%H %k %h %I %l');

754
 
time_format('100:00:00', '%H %k %h %I %l')

755
 
100 100 04 04 4

756
 
select timestampdiff(month,'2004-09-11','2004-09-11');

757
 
timestampdiff(month,'2004-09-11','2004-09-11')

758
 
0

759
 
select timestampdiff(month,'2004-09-11','2005-09-11');

760
 
timestampdiff(month,'2004-09-11','2005-09-11')

761
 
12

762
 
select timestampdiff(month,'2004-09-11','2006-09-11');

763
 
timestampdiff(month,'2004-09-11','2006-09-11')

764
 
24

765
 
select timestampdiff(month,'2004-09-11','2007-09-11');

766
 
timestampdiff(month,'2004-09-11','2007-09-11')

767
 
36

768
 
select timestampdiff(month,'2005-09-11','2004-09-11');

769
 
timestampdiff(month,'2005-09-11','2004-09-11')

770
 
-12

771
 
select timestampdiff(month,'2005-09-11','2003-09-11');

772
 
timestampdiff(month,'2005-09-11','2003-09-11')

773
 
-24

774
 
select timestampdiff(month,'2004-02-28','2005-02-28');

775
 
timestampdiff(month,'2004-02-28','2005-02-28')

776
 
12

777
 
select timestampdiff(month,'2004-02-29','2005-02-28');

778
 
timestampdiff(month,'2004-02-29','2005-02-28')

779
 
11

780
 
select timestampdiff(month,'2004-02-28','2005-02-28');

781
 
timestampdiff(month,'2004-02-28','2005-02-28')

782
 
12

783
 
select timestampdiff(month,'2004-03-29','2005-03-28');

784
 
timestampdiff(month,'2004-03-29','2005-03-28')

785
 
11

786
 
select timestampdiff(month,'2003-02-28','2004-02-29');

787
 
timestampdiff(month,'2003-02-28','2004-02-29')

788
 
12

789
 
select timestampdiff(month,'2003-02-28','2005-02-28');

790
 
timestampdiff(month,'2003-02-28','2005-02-28')

791
 
24

792
 
select timestampdiff(month,'1999-09-11','2001-10-10');

793
 
timestampdiff(month,'1999-09-11','2001-10-10')

794
 
24

795
 
select timestampdiff(month,'1999-09-11','2001-9-11');

796
 
timestampdiff(month,'1999-09-11','2001-9-11')

797
 
24

798
 
select timestampdiff(year,'1999-09-11','2001-9-11');

799
 
timestampdiff(year,'1999-09-11','2001-9-11')

800
 
2

801
 
select timestampdiff(year,'2004-02-28','2005-02-28');

802
 
timestampdiff(year,'2004-02-28','2005-02-28')

803
 
1

804
 
select timestampdiff(year,'2004-02-29','2005-02-28');

805
 
timestampdiff(year,'2004-02-29','2005-02-28')

806
 
0

807
 
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);

808
 
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);

809
 
INSERT INTO t1 VALUES

810
 
(1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');

811
 
INSERT INTO t2 VALUES

812
 
(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');

813
 
SELECT * FROM t1, t2 

814
 
WHERE t1.day BETWEEN 

815
 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;

816
 
id	day	id	day

817
 
1	2005-06-01	1	2005-08-01

818
 
3	2005-07-01	1	2005-08-01

819
 
1	2005-06-01	2	2005-06-15

820
 
1	2005-06-01	3	2005-07-15

821
 
3	2005-07-01	3	2005-07-15

822
 
SELECT * FROM t1, t2 

823
 
WHERE CAST(t1.day AS DATE) BETWEEN 

824
 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;

825
 
id	day	id	day

826
 
1	2005-06-01	1	2005-08-01

827
 
3	2005-07-01	1	2005-08-01

828
 
1	2005-06-01	2	2005-06-15

829
 
1	2005-06-01	3	2005-07-15

830
 
3	2005-07-01	3	2005-07-15

831
 
DROP TABLE t1,t2;

832
 
create table t1 (field DATE);

833
 
insert into t1 values ('2006-11-06');

834
 
select * from t1 where field < '2006-11-06 04:08:36.0';

835
 
field

836
 
2006-11-06

837
 
select * from t1 where field = '2006-11-06 04:08:36.0';

838
 
field

839
 
select * from t1 where field = '2006-11-06';

840
 
field

841
 
2006-11-06

842
 
select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';

843
 
field

844
 
2006-11-06

845
 
select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';

846
 
field

847
 
2006-11-06

848
 
drop table t1;

849
 
select DATE_ADD('20071108181000', INTERVAL 1 DAY);

850
 
DATE_ADD('20071108181000', INTERVAL 1 DAY)

851
 
2007-11-09 18:10:00

852
 
select DATE_ADD(20071108181000,   INTERVAL 1 DAY);

853
 
DATE_ADD(20071108181000,   INTERVAL 1 DAY)

854
 
2007-11-09 18:10:00

855
 
select DATE_ADD('20071108',       INTERVAL 1 DAY);

856
 
DATE_ADD('20071108',       INTERVAL 1 DAY)

857
 
2007-11-09

858
 
select DATE_ADD(20071108,         INTERVAL 1 DAY);

859
 
DATE_ADD(20071108,         INTERVAL 1 DAY)

860
 
2007-11-09

861
 
select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND;

862
 
LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND

863
 
2007-12-30 23:59:59

864
 
SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');

865
 
TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18')

866
 
2008-02-18 00:00:00.000001

867
 
SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');

868
 
TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18')

869
 
86400000000

870
 
SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);
629.2.6 by Monty
Updated test output with new and improved error messages. 
871
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
1 by brian
clean slate 
872
 
SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);
629.2.6 by Monty
Updated test output with new and improved error messages. 
873
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
1 by brian
clean slate 
874
 
SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND;
629.2.6 by Monty
Updated test output with new and improved error messages. 
875
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
1 by brian
clean slate 
876
 
SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
629.2.6 by Monty
Updated test output with new and improved error messages. 
877
 
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
1 by brian
clean slate 
878
 
End of 5.0 tests

879
 
select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);

880
 
date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)

881
 
0049-12-31 23:59:59

882
 
select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);

883
 
date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND)

884
 
0198-12-31 23:59:59

885
 
select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);

886
 
date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND)

887
 
0200-01-01 00:00:01

888
 
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);

889
 
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)

890
 
0199-12-31 23:59:59

891
 
select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);

892
 
date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND)

893
 
0200-01-01 00:00:00

894
 
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);

895
 
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)

896
 
0199-12-31 23:59:59

897
 
select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);

898
 
date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)

899
 
0001-01-01 23:59:59

900
 
select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);

901
 
date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND)

902
 
2049-12-31 23:59:59

903
 
select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);

904
 
date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND)

905
 
1989-12-31 23:59:59

906
 
select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);

907
 
date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND)

908
 
0068-12-31 23:59:59

909
 
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);

910
 
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)

911
 
0168-12-31 23:59:59

912
 
End of 5.1 tests