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 for column 't' at row 2
Warning	1264	Out of range value for column 't' at row 3
Warning	1264	Out of range value 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;
End of 4.1 tests
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;
#
# Bug #44792: valgrind warning when casting from time to time
#
CREATE TABLE t1 (c TIME);
INSERT INTO t1 VALUES ('0:00:00');
SELECT CAST(c AS TIME) FROM t1;
CAST(c AS TIME)
00:00:00
DROP TABLE t1;
End of 5.0 tests
#
# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
#
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
TIMESTAMP(f1,'1')
NULL
DROP TABLE t1;
End of 5.1 tests
CREATE TABLE t1 (f1 TIME);
INSERT INTO t1 VALUES ('24:00:00');
SELECT      '24:00:00' = (SELECT f1 FROM t1);
'24:00:00' = (SELECT f1 FROM t1)
1
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
0
TRUNCATE t1;
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)
0
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT '-24:00:00' = (SELECT f1 FROM t1);
'-24:00:00' = (SELECT f1 FROM t1)
1
DROP TABLE t1;
End of 6.0 tests