1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
drop table if exists t1, test;
select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")
2101112000123
select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")
101112000123
select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")
1112000123
select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")
12000123
select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")
123
select date_format("1997-12-31 23:59:59.000002", "%f");
date_format("1997-12-31 23:59:59.000002", "%f")
000002
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)
2025-05-23 04:40:39.000001
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)
1999-02-21 17:40:39.000001
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)
1998-01-07 22:41:39.000001
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)
1998-01-01 02:46:40.000001
select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)
1998-01-01 00:00:00.000001
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)
1997-12-30 22:58:58.999999
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)
1997-12-31 22:58:58.999999
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)
1997-12-31 23:58:58.999999
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)
1997-12-31 23:59:58.999999
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)
1997-12-31 23:59:59.999999
select adddate("1997-12-31 23:59:59.000001", 10);
adddate("1997-12-31 23:59:59.000001", 10)
1998-01-10 23:59:59.000001
select subdate("1997-12-31 23:59:59.000001", 10);
subdate("1997-12-31 23:59:59.000001", 10)
1997-12-21 23:59:59.000001
select datediff("1997-12-31 23:59:59.000001","1997-12-30");
datediff("1997-12-31 23:59:59.000001","1997-12-30")
1
select datediff("1997-11-30 23:59:59.000001","1997-12-31");
datediff("1997-11-30 23:59:59.000001","1997-12-31")
-31
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
ERROR HY000: Received an invalid datetime value '1997-11-31 23:59:59.000001'.
select datediff("1997-11-30 23:59:59.000001",null);
datediff("1997-11-30 23:59:59.000001",null)
NULL
select makedate(03,1);
makedate(03,1)
2003-01-01
select makedate('0003',1);
makedate('0003',1)
2003-01-01
select makedate(1997,1);
makedate(1997,1)
1997-01-01
select makedate(1997,0);
makedate(1997,0)
NULL
select makedate(9999,365);
makedate(9999,365)
9999-12-31
select makedate(9999,366);
makedate(9999,366)
NULL
select makedate(100,1);
makedate(100,1)
0100-01-01
select timestamp("2001-12-01 01:01:01.000100");
timestamp("2001-12-01 01:01:01.000100")
2001-12-01 01:01:01.000100
select timestamp("2001-12-01");
timestamp("2001-12-01")
2001-12-01 00:00:00
select day("1997-12-31 23:59:59.000001");
day("1997-12-31 23:59:59.000001")
31
select date("1997-12-31 23:59:59.000001");
date("1997-12-31 23:59:59.000001")
1997-12-31
select date("1997-13-31 23:59:59.000001");
date("1997-13-31 23:59:59.000001")
NULL
Warnings:
Warning 1292 Incorrect datetime value: '1997-13-31 23:59:59.000001'
select microsecond("1997-12-31 23:59:59.000001");
microsecond("1997-12-31 23:59:59.000001")
1
create table t1
select makedate(1997,1) as f1,
date("1997-12-31 23:59:59.000001") as f8;
describe t1;
Field Type Null Default Default_is_NULL On_Update
f1 DATE YES YES
f8 DATE YES YES
select * from t1;
f1 f8
1997-01-01 1997-12-31
create table test(t1 datetime, t4 datetime);
insert into test values
('2001-01-01 01:01:01', '2001-02-01 01:01:01'),
('2001-01-01 01:01:01', "1997-12-31 23:59:59.000001"),
('1997-12-31 23:59:59.000001', '2001-01-01 01:01:01'),
('2001-01-01 01:01:01', null),
('2001-01-01 01:01:01', '2001-01-01 01:01:01'),
('2001-01-01 01:01:01', null),
(null, null),
('2001-01-01 01:01:01', '2001-01-01 01:01:01');
drop table t1, test;
select microsecond("1997-12-31 23:59:59.01") as a;
a
10000
select microsecond(19971231235959.01) as a;
a
10000
select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
a
1997-12-31 00:00:10.090000
|