diff options
Diffstat (limited to 'mysql-test/main/type_timestamp_hires.result')
| -rw-r--r-- | mysql-test/main/type_timestamp_hires.result | 299 |
1 files changed, 297 insertions, 2 deletions
diff --git a/mysql-test/main/type_timestamp_hires.result b/mysql-test/main/type_timestamp_hires.result index fa6adc075ed..dc69aa36b0f 100644 --- a/mysql-test/main/type_timestamp_hires.result +++ b/mysql-test/main/type_timestamp_hires.result @@ -17,8 +17,8 @@ a 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 -select truncate(a, 6) from t1; -truncate(a, 6) +select cast(a AS double(30,6)) from t1; +cast(a AS double(30,6)) 0.000000 20101211002003.120000 20101211010203.457031 @@ -333,3 +333,298 @@ t1 CREATE TABLE `t1` ( `a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# Start of 10.4 tests +# +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +# ROUND(timestamp) and TRUNCATE(timestamp) currently return DATETIME. +# This may change in the future to return TIMESTAMP. +CREATE TABLE t1 (a1 TIMESTAMP(6) NULL DEFAULT '2001-01-01 00:00:00', a2 TIMESTAMP(6) NOT NULL); +CREATE TABLE t2 AS SELECT +ROUND(a1) AS r1, +ROUND(a2) AS r2, +TRUNCATE(a1,0) AS t1, +TRUNCATE(a2,0) AS t2 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `r1` datetime DEFAULT NULL, + `r2` datetime DEFAULT NULL, + `t1` datetime DEFAULT NULL, + `t2` datetime NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP(6)); +INSERT INTO t1 VALUES +('1970-01-01 00:00:01.999999'), +('2000-01-01 00:00:00.999999'), +('2000-01-01 23:59:59.999999'), +('2000-02-29 23:59:59.999999'), +('2000-12-31 23:59:59.999999'), +('2001-01-01 00:00:00.999999'), +('2001-01-01 23:59:59.999999'), +('2001-02-28 23:59:59.999999'), +('2001-12-31 23:59:59.999999'), +('2038-01-19 03:14:07.999999'); +SELECT a, TRUNCATE(a,0) FROM t1; +a TRUNCATE(a,0) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, TRUNCATE(a,1) FROM t1; +a TRUNCATE(a,1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9 +SELECT a, TRUNCATE(a,2) FROM t1; +a TRUNCATE(a,2) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99 +SELECT a, TRUNCATE(a,3) FROM t1; +a TRUNCATE(a,3) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999 +SELECT a, TRUNCATE(a,4) FROM t1; +a TRUNCATE(a,4) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9999 +SELECT a, TRUNCATE(a,5) FROM t1; +a TRUNCATE(a,5) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99999 +SELECT a, TRUNCATE(a,6) FROM t1; +a TRUNCATE(a,6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, TRUNCATE(a,7) FROM t1; +a TRUNCATE(a,7) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, TRUNCATE(a,-1) FROM t1; +a TRUNCATE(a,-1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, TRUNCATE(a,-6) FROM t1; +a TRUNCATE(a,-6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07 +SELECT a, ROUND(a) FROM t1; +a ROUND(a) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,0) FROM t1; +a ROUND(a,0) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,1) FROM t1; +a ROUND(a,1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0 +SELECT a, ROUND(a,2) FROM t1; +a ROUND(a,2) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00 +SELECT a, ROUND(a,3) FROM t1; +a ROUND(a,3) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.000 +SELECT a, ROUND(a,4) FROM t1; +a ROUND(a,4) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0000 +SELECT a, ROUND(a,5) FROM t1; +a ROUND(a,5) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00000 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00000 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00000 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00000 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00000 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00000 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00000 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00000 +SELECT a, ROUND(a,6) FROM t1; +a ROUND(a,6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, ROUND(a,7) FROM t1; +a ROUND(a,7) +1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999 +2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999 +2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999 +2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 +2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 +2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999 +2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999 +2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999 +2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 +2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999 +SELECT a, ROUND(a,-1) FROM t1; +a ROUND(a,-1) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +SELECT a, ROUND(a,-6) FROM t1; +a ROUND(a,-6) +1970-01-01 00:00:01.999999 1970-01-01 00:00:02 +2000-01-01 00:00:00.999999 2000-01-01 00:00:01 +2000-01-01 23:59:59.999999 2000-01-02 00:00:00 +2000-02-29 23:59:59.999999 2000-03-01 00:00:00 +2000-12-31 23:59:59.999999 2001-01-01 00:00:00 +2001-01-01 00:00:00.999999 2001-01-01 00:00:01 +2001-01-01 23:59:59.999999 2001-01-02 00:00:00 +2001-02-28 23:59:59.999999 2001-03-01 00:00:00 +2001-12-31 23:59:59.999999 2002-01-01 00:00:00 +2038-01-19 03:14:07.999999 2038-01-19 03:14:08 +DROP TABLE t1; +SET time_zone=DEFAULT; +# +# End of 10.4 tests +# |