2
# testing of the TIME column type
6
drop table if exists t1;
9
create table t1 (t time);
10
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");
11
insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
14
insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
18
create table t1 (t time);
19
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');
20
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
21
select sec_to_time(time_to_sec(t)) from t1;
25
# BUG #12440: Incorrect processing of time values containing
26
# long fraction part and/or large exponent part.
28
# These must return normal result:
29
# ##########################################################
30
# To be uncommented after fix BUG #15805
31
# ##########################################################
32
# SELECT CAST(235959.123456 AS TIME);
33
# SELECT CAST(0.235959123456e+6 AS TIME);
34
# SELECT CAST(235959123456e-6 AS TIME);
35
# These must cut fraction part and produce warning:
36
# SELECT CAST(235959.1234567 AS TIME);
37
# SELECT CAST(0.2359591234567e6 AS TIME);
38
# This must return NULL and produce warning:
39
# SELECT CAST(0.2359591234567e+30 AS TIME);
40
# ##########################################################
45
# Bug#29555: Comparing time values as strings may lead to a wrong result.
47
select cast('100:55:50' as time) < cast('24:00:00' as time);
48
select cast('100:55:50' as time) < cast('024:00:00' as time);
49
select cast('300:55:50' as time) < cast('240:00:00' as time);
50
select cast('100:55:50' as time) > cast('24:00:00' as time);
51
select cast('100:55:50' as time) > cast('024:00:00' as time);
52
select cast('300:55:50' as time) > cast('240:00:00' as time);
53
create table t1 (f1 time);
54
insert into t1 values ('24:00:00');
55
select cast('24:00:00' as time) = (select f1 from t1);
59
# Bug#29739: Incorrect time comparison in BETWEEN.
61
create table t1(f1 time, f2 time);
62
insert into t1 values('20:00:00','150:00:00');
63
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
67
# Bug#29729: Wrong conversion error led to an empty result set.
71
f3 int(11) unsigned NOT NULL default '0',
74
insert into t1 values('2007-07-01', 1);
75
insert into t1 values('2007-07-01', 2);
76
insert into t1 values('2007-07-02', 1);
77
insert into t1 values('2007-07-02', 2);
78
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
83
--echo # Bug #44792: valgrind warning when casting from time to time
86
CREATE TABLE t1 (c TIME);
87
INSERT INTO t1 VALUES ('0:00:00');
88
SELECT CAST(c AS TIME) FROM t1;
92
--echo End of 5.0 tests