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
|
drop table if exists t1;
create table t1 (t time);
insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34");
insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
select * from t1;
t
10:22:33
12:34:56
00:00:10
00:12:34
12:34:56
123:45:59
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
Warnings:
Warning 1265 Data truncated for column 't' at row 1
Warning 1264 Out of range value adjusted for column 't' at row 2
Warning 1264 Out of range value adjusted for column 't' at row 3
Warning 1264 Out of range value adjusted for column 't' at row 4
Warning 1265 Data truncated for column 't' at row 6
select * from t1;
t
10:22:33
12:34:56
00:00:10
00:12:34
12:34:56
123:45:59
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
00:00:10
00:00:00
838:59:59
838:59:59
262:22:00
00:00:12
drop table t1;
create table t1 (t time);
insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00');
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
t time_to_sec(t) sec_to_time(time_to_sec(t))
09:00:00 32400 09:00:00
13:00:00 46800 13:00:00
19:38:34 70714 19:38:34
13:00:00 46800 13:00:00
09:00:00 32400 09:00:00
09:00:00 32400 09:00:00
13:00:00 46800 13:00:00
13:00:00 46800 13:00:00
13:00:00 46800 13:00:00
09:00:00 32400 09:00:00
select sec_to_time(time_to_sec(t)) from t1;
sec_to_time(time_to_sec(t))
09:00:00
13:00:00
19:38:34
13:00:00
09:00:00
09:00:00
13:00:00
13:00:00
13:00:00
09:00:00
drop table t1;
select cast('100:55:50' as time) < cast('24:00:00' as time);
cast('100:55:50' as time) < cast('24:00:00' as time)
0
select cast('100:55:50' as time) < cast('024:00:00' as time);
cast('100:55:50' as time) < cast('024:00:00' as time)
0
select cast('300:55:50' as time) < cast('240:00:00' as time);
cast('300:55:50' as time) < cast('240:00:00' as time)
0
select cast('100:55:50' as time) > cast('24:00:00' as time);
cast('100:55:50' as time) > cast('24:00:00' as time)
1
select cast('100:55:50' as time) > cast('024:00:00' as time);
cast('100:55:50' as time) > cast('024:00:00' as time)
1
select cast('300:55:50' as time) > cast('240:00:00' as time);
cast('300:55:50' as time) > cast('240:00:00' as time)
1
create table t1 (f1 time);
insert into t1 values ('24:00:00');
select cast('24:00:00' as time) = (select f1 from t1);
cast('24:00:00' as time) = (select f1 from t1)
1
drop table t1;
create table t1(f1 time, f2 time);
insert into t1 values('20:00:00','150:00:00');
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
1
1
drop table t1;
CREATE TABLE t1 (
f2 date NOT NULL,
f3 int(11) unsigned NOT NULL default '0',
PRIMARY KEY (f3, f2)
);
insert into t1 values('2007-07-01', 1);
insert into t1 values('2007-07-01', 2);
insert into t1 values('2007-07-02', 1);
insert into t1 values('2007-07-02', 2);
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
sum(f3)
3
drop table t1;
|
