← 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;
1063.9.3 by Brian Aker
Partial fix for tests for tmp 
341
 
CREATE TEMPORARY TABLE t1 (updated text) ENGINE=MyISAM;
1 by brian
clean slate 
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)
907.1.7 by Jay Pipes
Merged in remove-timezone work 
385
 
1970-01-01 00:00:01
1 by brian
clean slate 
386
 
select unix_timestamp(@a);

387
 
unix_timestamp(@a)

388
 
1
907.1.7 by Jay Pipes
Merged in remove-timezone work 
389
 
select unix_timestamp('1970-01-01 00:00:01');

390
 
unix_timestamp('1970-01-01 00:00:01')

391
 
1
1 by brian
clean slate 
392
 
select from_unixtime(-1);

393
 
from_unixtime(-1)

394
 
NULL

395
 
select from_unixtime(2147483647);

396
 
from_unixtime(2147483647)
907.1.7 by Jay Pipes
Merged in remove-timezone work 
397
 
2038-01-19 03:14:07
1 by brian
clean slate 
398
 
select from_unixtime(2147483648);

399
 
from_unixtime(2147483648)

400
 
NULL

401
 
select from_unixtime(0);

402
 
from_unixtime(0)
907.1.7 by Jay Pipes
Merged in remove-timezone work 
403
 
1970-01-01 00:00:00
1 by brian
clean slate 
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');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
411
 
ERROR HY000: Received an invalid value '2039-01-20 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
412
 
select unix_timestamp('1968-01-20 01:00:00');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
413
 
ERROR HY000: Received an invalid value '1968-01-20 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
414
 
select unix_timestamp('2038-02-10 01:00:00');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
415
 
ERROR HY000: Received an invalid value '2038-02-10 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
416
 
select unix_timestamp('1969-11-20 01:00:00');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
417
 
ERROR HY000: Received an invalid value '1969-11-20 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
418
 
select unix_timestamp('2038-01-20 01:00:00');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
419
 
ERROR HY000: Received an invalid value '2038-01-20 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
420
 
select unix_timestamp('1969-12-30 01:00:00');
907.1.7 by Jay Pipes
Merged in remove-timezone work 
421
 
ERROR HY000: Received an invalid value '1969-12-30 01:00:00' for a UNIX timestamp.
1 by brian
clean slate 
422
 
select unix_timestamp('2038-01-17 12:00:00');

423
 
unix_timestamp('2038-01-17 12:00:00')
907.1.7 by Jay Pipes
Merged in remove-timezone work 
424
 
2147342400

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

426
 
unix_timestamp('2038-01-19 03:14:07')

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

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

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

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

435
 
1997-12-31 00:00:01

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

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

438
 
1999-01-31

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

440
 
date_add(datetime, INTERVAL 1 SECOND)

441
 
2001-01-02 03:04:06

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

443
 
date_add(datetime, INTERVAL 1 YEAR)

444
 
2002-01-02 03:04:05

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

446
 
date_add(date,INTERVAL 1 SECOND)

447
 
2003-01-02 00:00:01

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

449
 
date_add(date,INTERVAL 1 MINUTE)

450
 
2003-01-02 00:01:00

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

452
 
date_add(date,INTERVAL 1 HOUR)

453
 
2003-01-02 01:00:00

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

455
 
date_add(date,INTERVAL 1 DAY)

456
 
2003-01-03

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

458
 
date_add(date,INTERVAL 1 MONTH)

459
 
2003-02-02

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

461
 
date_add(date,INTERVAL 1 YEAR)

462
 
2004-01-02

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

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

465
 
2003-01-02 00:01:01

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

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

468
 
2003-01-02 01:01:00

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

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

471
 
2003-01-03 01:00:00

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

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

474
 
2004-02-02

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

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

477
 
2003-01-02 01:01:01

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

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

480
 
2003-01-03 01:01:00

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

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

483
 
2003-01-03 01:01:01

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

485
 
date_add(date,INTERVAL "1" WEEK)

486
 
2003-01-09

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

488
 
date_add(date,INTERVAL "1" QUARTER)

489
 
2003-04-02

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

491
 
timestampadd(MINUTE, 1, date)

492
 
2003-01-02 00:01:00

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

494
 
timestampadd(WEEK, 1, date)

495
 
2003-01-09

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

497
 
timestampadd(SQL_TSI_SECOND, 1, date)

498
 
2003-01-02 00:00:01

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

500
 
timestampadd(SQL_TSI_FRAC_SECOND, 1, date)

501
 
2003-01-02 00:00:00.000001

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

503
 
a

504
 
3

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

506
 
a

507
 
-1

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

509
 
a

510
 
-5

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

512
 
a

513
 
0

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

515
 
a

516
 
107

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

518
 
a

519
 
12

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

521
 
a

522
 
2136

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

524
 
a

525
 
89

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

527
 
a

528
 
128159

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

530
 
a

531
 
7689539

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

533
 
a

534
 
7689538999999

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

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

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

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

539
 
a1	a2	a3	a4

540
 
28	28	29	29

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

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

543
 
0

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

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

546
 
1

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

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

549
 
1

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

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

552
 
1

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

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

555
 
2

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

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

558
 
2

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

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

561
 
0

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

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

564
 
1

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

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

567
 
1

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

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

570
 
1

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

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

573
 
2

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

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

576
 
2

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

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

579
 
0

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

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

582
 
1

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

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

585
 
1

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

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

588
 
1

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

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

591
 
2

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

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

594
 
2

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

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

597
 
0

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

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

600
 
1

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

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

603
 
1

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

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

606
 
1

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

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

609
 
2

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

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

612
 
2

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

615
 
last_day('2000-02-05')

616
 
2000-02-29

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

618
 
last_day('2002-12-31')

619
 
2002-12-31

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

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

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

623
 
last_day('2003-04-01')

624
 
2003-04-30

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

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

627
 
2001-01-31

628
 
select last_day(NULL);

629
 
last_day(NULL)

630
 
NULL

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

632
 
last_day('2001-02-12')

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

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

636
 
describe t1;
1309.4.3 by Brian Aker
Refactor DESC to use new table. 
637
 
Field	Type	Null	Default	Default_is_NULL	On_Update
1660 by Brian Aker
MErge in change to do YES/NO like standard requires. 
638
 
a	DATE	NO		NO	

639
 
b	DATE	YES		YES
1 by brian
clean slate 
640
 
select * from t1;

641
 
a	b

642
 
2000-02-29	1996-01-01

643
 
drop table t1;

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

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

646
 
a	b

647
 
2000-02-29	1996-01-01

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

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

650
 
1998-01-01

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

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

653
 
10

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

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

656
 
19971231

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

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

659
 
19971231.0
907.1.8 by Jay Pipes
Final removal of timezones 
660
 
select strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0;

661
 
strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0

662
 
1

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

664
 
strcmp(date_format(date_sub(localtimestamp(), interval 0 hour),"%Y-%m-%d"), utc_date())=0
1 by brian
clean slate 
665
 
1

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

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

668
 
1
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
669
 
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 
670
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

671
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

672
 
Warnings:
896.5.1 by Jay Pipes
Removes the TIME column type and related time functions. 
673
 
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 
674
 
SET @TMP='2007-08-01 12:22:49';

675
 
CREATE TABLE t1 (d DATETIME);

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

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

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

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

680
 
count(*)

681
 
3

682
 
DROP TABLE t1;

683
 
select last_day('2005-00-00');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
684
 
ERROR HY000: Received an invalid datetime value '2005-00-00'.
1 by brian
clean slate 
685
 
select last_day('2005-00-01');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
686
 
ERROR HY000: Received an invalid datetime value '2005-00-01'.
1 by brian
clean slate 
687
 
select last_day('2005-01-00');
873.1.9 by Jay Pipes
This patch fixes the following functions to properly error out 
688
 
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. 
689
 
create table t1(f1 date, f3 datetime);

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

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

693
 
f1

694
 
2006-01-01

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

696
 
f1

697
 
2006-01-01

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

699
 
f1

700
 
2006-01-01

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

702
 
f3

703
 
2006-01-01 12:01:01

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

705
 
f3

706
 
2006-01-01 12:01:01

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

708
 
f1

709
 
2006-01-01

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

711
 
f1

712
 
2006-01-01

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

714
 
f1

715
 
Warnings:

716
 
Warning	1292	Incorrect datetime value: 'zzz'

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

718
 
f1

719
 
2006-01-01

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

721
 
f1

722
 
2006-01-02

723
 
drop table t1;

724
 
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. 
725
 
ERROR HY000: Received an invalid datetime value '100000:02:03'.
1 by brian
clean slate 
726
 
SHOW VARIABLES LIKE 'character_set_results';

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

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

731
 
fmtddate	field2

732
 
Sep-4 12:00AM	abcd

733
 
DROP TABLE testBug8868;

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

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

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

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

740
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

741
 
Warnings:

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

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

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

745
 
100 100 04 04 4

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

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

748
 
0

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

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

751
 
12

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

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

754
 
24

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

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

757
 
36

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

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

760
 
-12

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

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

763
 
-24

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

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

766
 
12

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

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

769
 
11

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

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

772
 
12

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

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

775
 
11

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

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

778
 
12

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

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

781
 
24

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

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

784
 
24

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

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

787
 
24

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

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

790
 
2

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

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

793
 
1

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

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

796
 
0

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

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

799
 
INSERT INTO t1 VALUES

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

801
 
INSERT INTO t2 VALUES

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

803
 
SELECT * FROM t1, t2 

804
 
WHERE t1.day BETWEEN 

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

806
 
id	day	id	day

807
 
1	2005-06-01	1	2005-08-01

808
 
1	2005-06-01	2	2005-06-15

809
 
1	2005-06-01	3	2005-07-15
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
810
 
3	2005-07-01	1	2005-08-01
1 by brian
clean slate 
811
 
3	2005-07-01	3	2005-07-15

812
 
SELECT * FROM t1, t2 

813
 
WHERE CAST(t1.day AS DATE) BETWEEN 

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

815
 
id	day	id	day

816
 
1	2005-06-01	1	2005-08-01

817
 
1	2005-06-01	2	2005-06-15

818
 
1	2005-06-01	3	2005-07-15
1718.2.1 by Lee Bieber
For the feature request (https://blueprints.launchpad.net/drizzle/+spec/limit-maximum-sort-size) 
819
 
3	2005-07-01	1	2005-08-01
1 by brian
clean slate 
820
 
3	2005-07-01	3	2005-07-15

821
 
DROP TABLE t1,t2;

822
 
create table t1 (field DATE);

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

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

825
 
field

826
 
2006-11-06

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

828
 
field

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

830
 
field

831
 
2006-11-06

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

833
 
field

834
 
2006-11-06

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

836
 
field

837
 
2006-11-06

838
 
drop table t1;

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

840
 
DATE_ADD('20071108181000', INTERVAL 1 DAY)

841
 
2007-11-09 18:10:00

842
 
select DATE_ADD(20071108181000,   INTERVAL 1 DAY);

843
 
DATE_ADD(20071108181000,   INTERVAL 1 DAY)

844
 
2007-11-09 18:10:00

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

846
 
DATE_ADD('20071108',       INTERVAL 1 DAY)

847
 
2007-11-09

848
 
select DATE_ADD(20071108,         INTERVAL 1 DAY);

849
 
DATE_ADD(20071108,         INTERVAL 1 DAY)

850
 
2007-11-09

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

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

853
 
2007-12-30 23:59:59

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

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

856
 
2008-02-18 00:00:00.000001

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

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

859
 
86400000000

860
 
SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);
629.2.6 by Monty
Updated test output with new and improved error messages. 
861
 
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 
862
 
SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);
629.2.6 by Monty
Updated test output with new and improved error messages. 
863
 
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 
864
 
SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND;
629.2.6 by Monty
Updated test output with new and improved error messages. 
865
 
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 
866
 
SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
629.2.6 by Monty
Updated test output with new and improved error messages. 
867
 
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 
868
 
End of 5.0 tests

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

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

871
 
0049-12-31 23:59:59

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

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

874
 
0198-12-31 23:59:59

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

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

877
 
0200-01-01 00:00:01

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

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

880
 
0199-12-31 23:59:59

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

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

883
 
0200-01-01 00:00:00

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

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

886
 
0199-12-31 23:59:59

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

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

889
 
0001-01-01 23:59:59

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

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

892
 
2049-12-31 23:59:59

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

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

895
 
1989-12-31 23:59:59

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

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

898
 
0068-12-31 23:59:59

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

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

901
 
0168-12-31 23:59:59

902
 
End of 5.1 tests