1 2let type=timestamp; 3--source include/type_hrtime.inc 4 5set time_zone='+03:00'; 6set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; 7 8create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP); 9# 10# CREATE ... DEFAULT NOW(X) 11# 12 13insert t1 values (); 14select * from t1; 15drop table t1; 16 17# 18# MDEV-438 Microseconds: Precision is ignored in CURRENT_TIMESTAMP(N) when it is given as a default column value 19# 20create or replace table t1 (a timestamp(5) default current_timestamp); 21show create table t1; 22create or replace table t1 (a timestamp(5) default current_timestamp()); 23show create table t1; 24create or replace table t1 (a timestamp(5) default current_timestamp(2)); 25show create table t1; 26insert t1 () values (); 27select * from t1; 28create or replace table t1 (a timestamp(5) default current_timestamp(5)); 29show create table t1; 30create or replace table t1 (a timestamp(5) default current_timestamp(6)); 31show create table t1; 32create or replace table t1 (a timestamp(5) on update current_timestamp); 33show create table t1; 34create or replace table t1 (a timestamp(5) on update current_timestamp()); 35show create table t1; 36--error ER_INVALID_ON_UPDATE 37create or replace table t1 (a timestamp(5) on update current_timestamp(3)); 38create or replace table t1 (a timestamp(5) on update current_timestamp(5)); 39show create table t1; 40create or replace table t1 (a timestamp(5) on update current_timestamp(6)); 41show create table t1; 42drop table t1; 43 44 45--echo # 46--echo # Start of 10.4 tests 47--echo # 48 49--echo # 50--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() 51--echo # 52 53--echo # ROUND(timestamp) and TRUNCATE(timestamp) currently return DATETIME. 54--echo # This may change in the future to return TIMESTAMP. 55 56CREATE TABLE t1 (a1 TIMESTAMP(6) NULL DEFAULT '2001-01-01 00:00:00', a2 TIMESTAMP(6) NOT NULL); 57CREATE TABLE t2 AS SELECT 58 ROUND(a1) AS r1, 59 ROUND(a2) AS r2, 60 TRUNCATE(a1,0) AS t1, 61 TRUNCATE(a2,0) AS t2 62FROM t1; 63SHOW CREATE TABLE t2; 64DROP TABLE t2; 65DROP TABLE t1; 66 67 68SET time_zone='+00:00'; 69 70CREATE TABLE t1 (a TIMESTAMP(6)); 71INSERT INTO t1 VALUES 72('1970-01-01 00:00:01.999999'), 73('2000-01-01 00:00:00.999999'), 74('2000-01-01 23:59:59.999999'), 75('2000-02-29 23:59:59.999999'), 76('2000-12-31 23:59:59.999999'), 77('2001-01-01 00:00:00.999999'), 78('2001-01-01 23:59:59.999999'), 79('2001-02-28 23:59:59.999999'), 80('2001-12-31 23:59:59.999999'), 81('2038-01-19 03:14:07.999999'); 82 83SELECT a, TRUNCATE(a,0) FROM t1; 84SELECT a, TRUNCATE(a,1) FROM t1; 85SELECT a, TRUNCATE(a,2) FROM t1; 86SELECT a, TRUNCATE(a,3) FROM t1; 87SELECT a, TRUNCATE(a,4) FROM t1; 88SELECT a, TRUNCATE(a,5) FROM t1; 89SELECT a, TRUNCATE(a,6) FROM t1; 90SELECT a, TRUNCATE(a,7) FROM t1; 91SELECT a, TRUNCATE(a,-1) FROM t1; 92SELECT a, TRUNCATE(a,-6) FROM t1; 93 94SELECT a, ROUND(a) FROM t1; 95SELECT a, ROUND(a,0) FROM t1; 96SELECT a, ROUND(a,1) FROM t1; 97SELECT a, ROUND(a,2) FROM t1; 98SELECT a, ROUND(a,3) FROM t1; 99SELECT a, ROUND(a,4) FROM t1; 100SELECT a, ROUND(a,5) FROM t1; 101SELECT a, ROUND(a,6) FROM t1; 102SELECT a, ROUND(a,7) FROM t1; 103SELECT a, ROUND(a,-1) FROM t1; 104SELECT a, ROUND(a,-6) FROM t1; 105 106DROP TABLE t1; 107 108SET time_zone=DEFAULT; 109 110 111--echo # 112--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() 113--echo # 114 115CREATE TABLE t1 (a TIMESTAMP NOT NULL); 116CREATE TABLE t2 AS SELECT FLOOR(a), CEILING(a) FROM t1; 117SHOW CREATE TABLE t2; 118DROP TABLE t1, t2; 119 120--echo # 121--echo # End of 10.4 tests 122--echo # 123