← Back to branch summary

~drizzle-trunk/drizzle/development

1 by brian
clean slate 
1
 
drop table if exists t1, test;

2
 
select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");

3
 
extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")

4
 
2101112000123

5
 
select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");

6
 
extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")

7
 
101112000123

8
 
select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");

9
 
extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")

10
 
1112000123

11
 
select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");

12
 
extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")

13
 
12000123

14
 
select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");

15
 
extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")

16
 
123

17
 
select date_format("1997-12-31 23:59:59.000002", "%f");

18
 
date_format("1997-12-31 23:59:59.000002", "%f")

19
 
000002

20
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);

21
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)

22
 
2025-05-23 04:40:39.000001

23
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);

24
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)

25
 
1999-02-21 17:40:39.000001

26
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);

27
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)

28
 
1998-01-07 22:41:39.000001

29
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);

30
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)

31
 
1998-01-01 02:46:40.000001

32
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);

33
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)

34
 
1998-01-01 00:00:00.000001

35
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);

36
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)

37
 
1997-12-30 22:58:58.999999

38
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);

39
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)

40
 
1997-12-31 22:58:58.999999

41
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);

42
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)

43
 
1997-12-31 23:58:58.999999

44
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);

45
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)

46
 
1997-12-31 23:59:58.999999

47
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);

48
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)

49
 
1997-12-31 23:59:59.999999

50
 
select adddate("1997-12-31 23:59:59.000001", 10);

51
 
adddate("1997-12-31 23:59:59.000001", 10)

52
 
1998-01-10 23:59:59.000001

53
 
select subdate("1997-12-31 23:59:59.000001", 10);

54
 
subdate("1997-12-31 23:59:59.000001", 10)

55
 
1997-12-21 23:59:59.000001

56
 
select datediff("1997-12-31 23:59:59.000001","1997-12-30");

57
 
datediff("1997-12-31 23:59:59.000001","1997-12-30")

58
 
1

59
 
select datediff("1997-11-30 23:59:59.000001","1997-12-31");

60
 
datediff("1997-11-30 23:59:59.000001","1997-12-31")

61
 
-31

62
 
SET @@SQL_MODE="ALLOW_INVALID_DATES";

63
 
select datediff("1997-11-31 23:59:59.000001","1997-12-31");

64
 
datediff("1997-11-31 23:59:59.000001","1997-12-31")

65
 
-30

66
 
SET @@SQL_MODE="";

67
 
select datediff("1997-11-31 23:59:59.000001","1997-12-31");

68
 
datediff("1997-11-31 23:59:59.000001","1997-12-31")

69
 
NULL

70
 
Warnings:

71
 
Warning	1292	Incorrect datetime value: '1997-11-31 23:59:59.000001'

72
 
select datediff("1997-11-30 23:59:59.000001",null);

73
 
datediff("1997-11-30 23:59:59.000001",null)

74
 
NULL

75
 
select weekofyear("1997-11-30 23:59:59.000001");

76
 
weekofyear("1997-11-30 23:59:59.000001")

77
 
48

78
 
select makedate(03,1);

79
 
makedate(03,1)

80
 
2003-01-01

81
 
select makedate('0003',1);

82
 
makedate('0003',1)

83
 
2003-01-01

84
 
select makedate(1997,1);

85
 
makedate(1997,1)

86
 
1997-01-01

87
 
select makedate(1997,0);

88
 
makedate(1997,0)

89
 
NULL

90
 
select makedate(9999,365);

91
 
makedate(9999,365)

92
 
9999-12-31

93
 
select makedate(9999,366);

94
 
makedate(9999,366)

95
 
NULL

96
 
select makedate(100,1);

97
 
makedate(100,1)

98
 
0100-01-01

99
 
select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");

100
 
addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002")

101
 
1998-01-02 01:01:01.000001

102
 
select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");

103
 
subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002")

104
 
1997-12-30 22:58:57.999999

105
 
select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");

106
 
addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")

107
 
NULL

108
 
select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");

109
 
subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")

110
 
NULL

111
 
select subtime("01:00:00.999999", "02:00:00.999998");

112
 
subtime("01:00:00.999999", "02:00:00.999998")

113
 
-00:59:59.999999

114
 
select subtime("02:01:01.999999", "01:01:01.999999");

115
 
subtime("02:01:01.999999", "01:01:01.999999")

116
 
01:00:00.000000

117
 
select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");

118
 
timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002")

119
 
838:59:59

120
 
Warnings:

121
 
Warning	1292	Truncated incorrect time value: '8807:59:59.999999'

122
 
select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");

123
 
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002")

124
 
46:58:57.999999

125
 
select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");

126
 
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002")

127
 
-24:00:00.000001

128
 
select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");

129
 
timediff("1997-12-31 23:59:59.000001","23:59:59.000001")

130
 
NULL

131
 
select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001");

132
 
timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001")

133
 
-00:00:00.000001

134
 
select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50");

135
 
timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50")

136
 
-00:00:00.000001

137
 
select maketime(10,11,12);

138
 
maketime(10,11,12)

139
 
10:11:12

140
 
select maketime(25,11,12);

141
 
maketime(25,11,12)

142
 
25:11:12

143
 
select maketime(-25,11,12);

144
 
maketime(-25,11,12)

145
 
-25:11:12

146
 
select timestamp("2001-12-01", "01:01:01.999999");

147
 
timestamp("2001-12-01", "01:01:01.999999")

148
 
2001-12-01 01:01:01.999999

149
 
select timestamp("2001-13-01", "01:01:01.000001");

150
 
timestamp("2001-13-01", "01:01:01.000001")

151
 
NULL

152
 
Warnings:

153
 
Warning	1292	Incorrect datetime value: '2001-13-01'

154
 
select timestamp("2001-12-01", "25:01:01");

155
 
timestamp("2001-12-01", "25:01:01")

156
 
2001-12-02 01:01:01

157
 
select timestamp("2001-12-01 01:01:01.000100");

158
 
timestamp("2001-12-01 01:01:01.000100")

159
 
2001-12-01 01:01:01.000100

160
 
select timestamp("2001-12-01");

161
 
timestamp("2001-12-01")

162
 
2001-12-01 00:00:00

163
 
select day("1997-12-31 23:59:59.000001");

164
 
day("1997-12-31 23:59:59.000001")

165
 
31

166
 
select date("1997-12-31 23:59:59.000001");

167
 
date("1997-12-31 23:59:59.000001")

168
 
1997-12-31

169
 
select date("1997-13-31 23:59:59.000001");

170
 
date("1997-13-31 23:59:59.000001")

171
 
NULL

172
 
Warnings:

173
 
Warning	1292	Incorrect datetime value: '1997-13-31 23:59:59.000001'

174
 
select time("1997-12-31 23:59:59.000001");

175
 
time("1997-12-31 23:59:59.000001")

176
 
23:59:59.000001

177
 
select time("1997-12-31 25:59:59.000001");

178
 
time("1997-12-31 25:59:59.000001")

179
 
NULL

180
 
Warnings:

181
 
Warning	1292	Truncated incorrect time value: '1997-12-31 25:59:59.000001'

182
 
select microsecond("1997-12-31 23:59:59.000001");

183
 
microsecond("1997-12-31 23:59:59.000001")

184
 
1

185
 
create table t1 

186
 
select makedate(1997,1) as f1,

187
 
addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,

188
 
addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,

189
 
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,

190
 
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,

191
 
maketime(10,11,12) as f6,

192
 
timestamp(cast("2001-12-01" as date), "01:01:01") as f7,

193
 
date("1997-12-31 23:59:59.000001") as f8,

194
 
time("1997-12-31 23:59:59.000001") as f9;

195
 
describe t1;

196
 
Field	Type	Null	Key	Default	Extra

197
 
f1	date	NO		0000-00-00	

198
 
f2	datetime	YES		NULL	

199
 
f3	time	YES		NULL	

200
 
f4	time	YES		NULL	

201
 
f5	time	YES		NULL	

202
 
f6	time	YES		NULL	

203
 
f7	datetime	YES		NULL	

204
 
f8	date	YES		NULL	

205
 
f9	time	YES		NULL	

206
 
select * from t1;

207
 
f1	f2	f3	f4	f5	f6	f7	f8	f9

208
 
1997-01-01	1998-01-02 01:01:00	49:01:01	46:58:57	-24:00:00	10:11:12	2001-12-01 01:01:01	1997-12-31	23:59:59

209
 
create table test(t1 datetime, t2 time, t3 time, t4 datetime);

210
 
insert into test values 

211
 
('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),

212
 
('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),

213
 
('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),

214
 
('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),

215
 
('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'),

216
 
('2001-01-01 01:01:01', null, '-1 01:01:01', null),

217
 
(null, null, null, null),

218
 
('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');

219
 
SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;

220
 
ttt	qqq

221
 
2001-01-01 02:02:02	NULL

222
 
2001-01-01 00:00:00	-25:01:00

223
 
1997-12-31 00:00:00	-25:01:00

224
 
2001-01-01 02:02:02	-24:00:00

225
 
2001-01-01 00:00:00	24:00:00

226
 
NULL	NULL

227
 
NULL	NULL

228
 
2001-01-01 02:02:02	26:02:02

229
 
SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq,

230
 
TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test;

231
 
ttt	qqq	eee	rrr

232
 
-744:00:00	NULL	NULL	NULL

233
 
838:59:59	22:58:58	-22:58:58	NULL

234
 
-838:59:59	-22:58:58	22:58:58	NULL

235
 
NULL	26:02:02	-26:02:02	NULL

236
 
00:00:00	-26:02:02	26:02:02	NULL

237
 
NULL	NULL	NULL	NULL

238
 
NULL	NULL	NULL	NULL

239
 
00:00:00	-24:00:00	24:00:00	NULL

240
 
Warnings:

241
 
Warning	1292	Truncated incorrect time value: '26305:01:02'

242
 
Warning	1292	Truncated incorrect time value: '-26305:01:02'

243
 
drop table t1, test;

244
 
select addtime("-01:01:01.01", "-23:59:59.1") as a;

245
 
a

246
 
-25:01:00.110000

247
 
select microsecond("1997-12-31 23:59:59.01") as a;

248
 
a

249
 
10000

250
 
select microsecond(19971231235959.01) as a;

251
 
a

252
 
10000

253
 
select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;

254
 
a

255
 
1997-12-31 00:00:10.090000

256
 
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");

257
 
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")

258
 
2003-01-02 10:11:12.001200