set time_zone='+03:00';
set timestamp=unix_timestamp('2011-01-01 01:01:01.123456');
select sec_to_time(12345), sec_to_time(12345.6789), sec_to_time(1234567e-2);
sec_to_time(12345)	03:25:45
sec_to_time(12345.6789)	03:25:45.6789
sec_to_time(1234567e-2)	03:25:45.670000
select now(), curtime(0), utc_timestamp(1), utc_time(2), current_time(3),
current_timestamp(4), localtime(5), localtimestamp(6), time_to_sec('12:34:56'),
time_to_sec('12:34:56.789');
now()	2011-01-01 01:01:01
curtime(0)	01:01:01
utc_timestamp(1)	2010-12-31 22:01:01.1
utc_time(2)	22:01:01.12
current_time(3)	01:01:01.123
current_timestamp(4)	2011-01-01 01:01:01.1234
localtime(5)	01:01:01.12345
localtimestamp(6)	2011-01-01 01:01:01.123456
time_to_sec('12:34:56')	45296
time_to_sec('12:34:56.789')	45296.789
select sec_to_time(time_to_sec('1:2:3')), sec_to_time(time_to_sec('2:3:4.567890'));
sec_to_time(time_to_sec('1:2:3'))	01:02:03
sec_to_time(time_to_sec('2:3:4.567890'))	02:03:04.567890
select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222));
time_to_sec(sec_to_time(11111))	11111
time_to_sec(sec_to_time(11111.22222))	11111.22222
select current_timestamp(7);
ERROR 42000: Too big precision specified for 'current_timestamp'. Maximum is 6
select curtime(7);
ERROR 42000: Too big precision specified for 'curtime'. Maximum is 6
drop table if exists t1;
create table t1 select sec_to_time(12345), sec_to_time(12345.6789),
sec_to_time(1234567e-2), now(), curtime(0),
utc_timestamp(1), utc_time(2), current_time(3),
current_timestamp(4), localtime(5), localtimestamp(6),
time_to_sec(123456), time_to_sec('12:34:56.789');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sec_to_time(12345)` time DEFAULT NULL,
  `sec_to_time(12345.6789)` time(4) DEFAULT NULL,
  `sec_to_time(1234567e-2)` time(6) DEFAULT NULL,
  `now()` timestamp NOT NULL,
  `curtime(0)` time NOT NULL,
  `utc_timestamp(1)` datetime(1) NOT NULL,
  `utc_time(2)` time(2) NOT NULL,
  `current_time(3)` time(3) NOT NULL,
  `current_timestamp(4)` timestamp(4) NOT NULL,
  `localtime(5)` time(5) NOT NULL,
  `localtimestamp(6)` datetime(6) NOT NULL,
  `time_to_sec(123456)` bigint(17) DEFAULT NULL,
  `time_to_sec('12:34:56.789')` decimal(19,3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select * from t1;
sec_to_time(12345)	03:25:45
sec_to_time(12345.6789)	03:25:45.6789
sec_to_time(1234567e-2)	03:25:45.670000
now()	2011-01-01 01:01:01
curtime(0)	01:01:01
utc_timestamp(1)	2010-12-31 22:01:01.1
utc_time(2)	22:01:01.12
current_time(3)	01:01:01.123
current_timestamp(4)	2011-01-01 01:01:01.1234
localtime(5)	01:01:01.12345
localtimestamp(6)	2011-01-01 01:01:01.123456
time_to_sec(123456)	45296
time_to_sec('12:34:56.789')	45296.789
drop table t1;
select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));;
unix_timestamp('2011-01-01 01:01:01')	1293832861
unix_timestamp('2011-01-01 01:01:01.123456')	1293832861.123456
unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0)))	1293832861
unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)))	1293832861.1234
select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));;
from_unixtime(unix_timestamp('2011/1/1 1:1:1'))	2011-01-01 01:01:01
from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456'))	2011-01-01 01:01:01.123456
from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0))))	2011-01-01 01:01:01
from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))))	2011-01-01 01:01:01.1234
select sec_to_time(3020399.99999), sec_to_time(3020399.999999), sec_to_time(3020399.9999999);
sec_to_time(3020399.99999)	sec_to_time(3020399.999999)	sec_to_time(3020399.9999999)
838:59:59.99999	838:59:59.999999	838:59:59.999999
select sec_to_time(-3020399.99999), sec_to_time(-3020399.999999), sec_to_time(-3020399.9999999);
sec_to_time(-3020399.99999)	sec_to_time(-3020399.999999)	sec_to_time(-3020399.9999999)
-838:59:59.99999	-838:59:59.999999	-838:59:59.999999
select 20010101000203.000000004 + interval 1 day;
20010101000203.000000004 + interval 1 day
2001-01-02 00:02:03.000000
select 20010101000203.4 + interval 1 day;
20010101000203.4 + interval 1 day
2001-01-02 00:02:03.4
set @a=cast('2011-01-02 12:13:14' as datetime);
select @a + interval 1 minute;
@a + interval 1 minute
2011-01-02 12:14:14
select @a + interval 10 microsecond;
@a + interval 10 microsecond
2011-01-02 12:13:14.000010
select @a + interval 10 microsecond + interval 999990 microsecond;
@a + interval 10 microsecond + interval 999990 microsecond
2011-01-02 12:13:15.000000
set @a='2011-01-02 12:13:14.123456';
create table t1 select CAST(@a AS DATETIME) as dauto,
CAST(@a AS DATETIME(0)) as d0,
CAST(@a AS DATETIME(1)) as d1,
CAST(@a AS DATETIME(2)) as d2,
CAST(@a AS DATETIME(3)) as d3,
CAST(@a AS DATETIME(4)) as d4,
CAST(@a AS DATETIME(5)) as d5,
CAST(@a AS DATETIME(6)) as d6,
CAST(@a AS TIME) as tauto,
CAST(@a AS TIME(0)) as t0,
CAST(@a AS TIME(1)) as t1,
CAST(@a AS TIME(2)) as t2,
CAST(@a AS TIME(3)) as t3,
CAST(@a AS TIME(4)) as t4,
CAST(@a AS TIME(5)) as t5,
CAST(@a AS TIME(6)) as t6;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `dauto` datetime DEFAULT NULL,
  `d0` datetime DEFAULT NULL,
  `d1` datetime(1) DEFAULT NULL,
  `d2` datetime(2) DEFAULT NULL,
  `d3` datetime(3) DEFAULT NULL,
  `d4` datetime(4) DEFAULT NULL,
  `d5` datetime(5) DEFAULT NULL,
  `d6` datetime(6) DEFAULT NULL,
  `tauto` time DEFAULT NULL,
  `t0` time DEFAULT NULL,
  `t1` time(1) DEFAULT NULL,
  `t2` time(2) DEFAULT NULL,
  `t3` time(3) DEFAULT NULL,
  `t4` time(4) DEFAULT NULL,
  `t5` time(5) DEFAULT NULL,
  `t6` time(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select * from t1;
dauto	2011-01-02 12:13:14
d0	2011-01-02 12:13:14
d1	2011-01-02 12:13:14.1
d2	2011-01-02 12:13:14.12
d3	2011-01-02 12:13:14.123
d4	2011-01-02 12:13:14.1234
d5	2011-01-02 12:13:14.12345
d6	2011-01-02 12:13:14.123456
tauto	12:13:14
t0	12:13:14
t1	12:13:14.1
t2	12:13:14.12
t3	12:13:14.123
t4	12:13:14.1234
t5	12:13:14.12345
t6	12:13:14.123456
drop table t1;
explain extended select cast(cast(@a as datetime(4)) as time(0));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select cast(cast(@`a` as datetime(4)) as time) AS `cast(cast(@a as datetime(4)) as time(0))`
select cast(cast(@a as time(2)) as time(6));
cast(cast(@a as time(2)) as time(6))
12:13:14.120000
select CAST(@a AS DATETIME(7));
ERROR 42000: Too big precision specified for '@`a`'. Maximum is 6
SELECT CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00') as exp;
exp
2011-01-02 15:00:00
SELECT CONVERT_TZ('2011-01-02 12:00:00.123', '+00:00', '+03:00') as exp;
exp
2011-01-02 15:00:00.123
SELECT CONVERT_TZ('2011-01-02 12:00:00.123456', '+00:00', '+03:00') as exp;
exp
2011-01-02 15:00:00.123456
SELECT CONVERT_TZ(CAST('2010-10-10 10:10:10.123456' AS DATETIME(4)), '+00:00', '+03:00') as exp;
exp
2010-10-10 13:10:10.1234
create table t1 (a varchar(200));
insert t1 values (now(6));
select * from t1;
a
2011-01-01 01:01:01.123456
drop table t1;
create table t1 (f1 timestamp(6));
insert into t1 values ('2002-07-15 21:00:00');
select time(f1) from t1;
time(f1)
21:00:00.000000
select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
time(f1)
21:00:00.000000
21:00:01.000000
alter table t1 modify f1 timestamp;
select time(f1) from t1;
time(f1)
21:00:00
select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
time(f1)
21:00:00
21:00:01
alter table t1 modify f1 varchar(100);
select time(f1) from t1;
time(f1)
21:00:00.000000
select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
time(f1)
21:00:00.000000
21:00:01.000000
drop table t1;
#
# Start of 10.3 tests
#
#
# MDEV-10182 Bad value when inserting COALESCE(CURRENT_TIMESTAMP) into a DECIMAL column
#
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.000000');
CREATE TABLE t1 (a DECIMAL(30,0));
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP(6));
INSERT INTO t1 VALUES (COALESCE(CURRENT_TIMESTAMP(6)));
SELECT * FROM t1;
a
20010101102030
20010101102030
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# End of 10.3 tests
#