|
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 |
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
|
|
|
873.1.9
by Jay Pipes
This patch fixes the following functions to properly error out |
63 |
ERROR HY000: Received an invalid datetime value '1997-11-31 23:59:59.000001'.
|
|
1
by brian
clean slate |
64 |
select datediff("1997-11-30 23:59:59.000001",null);
|
65 |
datediff("1997-11-30 23:59:59.000001",null)
|
|
66 |
NULL
|
|
67 |
select makedate(03,1);
|
|
68 |
makedate(03,1)
|
|
69 |
2003-01-01
|
|
70 |
select makedate('0003',1);
|
|
71 |
makedate('0003',1)
|
|
72 |
2003-01-01
|
|
73 |
select makedate(1997,1);
|
|
74 |
makedate(1997,1)
|
|
75 |
1997-01-01
|
|
76 |
select makedate(1997,0);
|
|
77 |
makedate(1997,0)
|
|
78 |
NULL
|
|
79 |
select makedate(9999,365);
|
|
80 |
makedate(9999,365)
|
|
81 |
9999-12-31
|
|
82 |
select makedate(9999,366);
|
|
83 |
makedate(9999,366)
|
|
84 |
NULL
|
|
85 |
select makedate(100,1);
|
|
86 |
makedate(100,1)
|
|
87 |
0100-01-01
|
|
88 |
select timestamp("2001-12-01 01:01:01.000100");
|
|
89 |
timestamp("2001-12-01 01:01:01.000100")
|
|
90 |
2001-12-01 01:01:01.000100
|
|
91 |
select timestamp("2001-12-01");
|
|
92 |
timestamp("2001-12-01")
|
|
93 |
2001-12-01 00:00:00
|
|
94 |
select day("1997-12-31 23:59:59.000001");
|
|
95 |
day("1997-12-31 23:59:59.000001")
|
|
96 |
31
|
|
97 |
select date("1997-12-31 23:59:59.000001");
|
|
98 |
date("1997-12-31 23:59:59.000001")
|
|
99 |
1997-12-31
|
|
100 |
select date("1997-13-31 23:59:59.000001");
|
|
101 |
date("1997-13-31 23:59:59.000001")
|
|
102 |
NULL
|
|
103 |
Warnings:
|
|
104 |
Warning 1292 Incorrect datetime value: '1997-13-31 23:59:59.000001'
|
|
105 |
select microsecond("1997-12-31 23:59:59.000001");
|
|
106 |
microsecond("1997-12-31 23:59:59.000001")
|
|
107 |
1
|
|
108 |
create table t1
|
|
109 |
select makedate(1997,1) as f1,
|
|
|
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
110 |
date("1997-12-31 23:59:59.000001") as f8;
|
|
1
by brian
clean slate |
111 |
describe t1;
|
|
1309.4.3
by Brian Aker
Refactor DESC to use new table. |
112 |
Field Type Null Default Default_is_NULL On_Update
|
|
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
113 |
f1 DATE YES YES
|
114 |
f8 DATE YES YES
|
|
|
1
by brian
clean slate |
115 |
select * from t1;
|
|
907.1.2
by Jay Pipes
Merging in old r902 temporal changes |
116 |
f1 f8
|
117 |
1997-01-01 1997-12-31
|
|
|
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
118 |
create table test(t1 datetime, t4 datetime);
|
|
1
by brian
clean slate |
119 |
insert into test values
|
|
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
120 |
('2001-01-01 01:01:01', '2001-02-01 01:01:01'),
|
121 |
('2001-01-01 01:01:01', "1997-12-31 23:59:59.000001"),
|
|
122 |
('1997-12-31 23:59:59.000001', '2001-01-01 01:01:01'),
|
|
123 |
('2001-01-01 01:01:01', null),
|
|
124 |
('2001-01-01 01:01:01', '2001-01-01 01:01:01'),
|
|
125 |
('2001-01-01 01:01:01', null),
|
|
126 |
(null, null),
|
|
127 |
('2001-01-01 01:01:01', '2001-01-01 01:01:01');
|
|
|
1
by brian
clean slate |
128 |
drop table t1, test;
|
129 |
select microsecond("1997-12-31 23:59:59.01") as a;
|
|
130 |
a
|
|
131 |
10000
|
|
132 |
select microsecond(19971231235959.01) as a;
|
|
133 |
a
|
|
134 |
10000
|
|
135 |
select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
|
|
136 |
a
|
|
137 |
1997-12-31 00:00:10.090000
|