以Postgresql为主,再聊聊资料库 关于MySQL timestamp型态精度与进位

今天看到FB社群中有讨论到 between, 进而有讨论到关于精度的问题.
使用 MySQL 做了一些测试,与大家分享.

-- 使用 MySQLselect version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+create table it200212 (  id int unsigned not null auto_increment primary key, its timestamp not null default current_timestamp);insert into it200212 (its) values(timestamp '2019-02-12 00:00:00'),(timestamp '2019-02-12 23:59:59.999999'),(timestamp '2019-02-13 00:00:00'),(timestamp '2019-02-13 12:59:59.999999');select * from it200212;+----+---------------------+| id | its                 |+----+---------------------+|  1 | 2019-02-12 00:00:00 ||  2 | 2019-02-13 00:00:00 ||  3 | 2019-02-13 00:00:00 ||  4 | 2019-02-13 13:00:00 |+----+---------------------+4 rows in set (0.00 sec)SET sql_mode='TIME_TRUNCATE_FRACTIONAL';-- 参考 https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractionalinsert into it200212 (its) values(timestamp '2019-02-22 00:00:00'),(timestamp '2019-02-22 23:59:59.999999'),(timestamp '2019-02-23 00:00:00'),(timestamp '2019-02-23 12:59:59.999999');select * from it200212;+----+---------------------+| id | its                 |+----+---------------------+|  1 | 2019-02-12 00:00:00 ||  2 | 2019-02-13 00:00:00 ||  3 | 2019-02-13 00:00:00 ||  4 | 2019-02-13 13:00:00 ||  5 | 2019-02-22 00:00:00 ||  6 | 2019-02-22 23:59:59 ||  7 | 2019-02-23 00:00:00 ||  8 | 2019-02-23 12:59:59 |+----+---------------------+8 rows in set (0.00 sec)desc it200212;+-------+------------------+------+-----+-------------------+-------------------+| Field | Type             | Null | Key | Default           | Extra             |+-------+------------------+------+-----+-------------------+-------------------+| id    | int(10) unsigned | NO   | PRI | NULL              | auto_increment    || its   | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |+-------+------------------+------+-----+-------------------+-------------------+2 rows in set (0.06 sec)create table it200212a (  id int unsigned not null auto_increment primary key, its timestamp(6) not null default current_timestamp(6));insert into it200212a (its) values(timestamp '2019-02-12 00:00:00'),(timestamp '2019-02-12 23:59:59.999999'),(timestamp '2019-02-13 00:00:00'),(timestamp '2019-02-13 12:59:59.999999');select * from it200212a;+----+----------------------------+| id | its                        |+----+----------------------------+|  1 | 2019-02-12 00:00:00.000000 ||  2 | 2019-02-12 23:59:59.999999 ||  3 | 2019-02-13 00:00:00.000000 ||  4 | 2019-02-13 12:59:59.999999 |+----+----------------------------+4 rows in set (0.00 sec)desc it200212a;+-------+------------------+------+-----+----------------------+-------------------+| Field | Type             | Null | Key | Default              | Extra             |+-------+------------------+------+-----+----------------------+-------------------+| id    | int(10) unsigned | NO   | PRI | NULL                 | auto_increment    || its   | timestamp(6)     | NO   |     | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED |+-------+------------------+------+-----+----------------------+-------------------+-- Let's try betweenselect *  from (select "it200212" as tbl             , id, its          from it200212         union all        select "it200212a"             , id, its          from it200212a) a where its between timestamp '2019-02-12 00:00:00'   and timestamp '2019-02-13 00:00:00';+-----------+----+----------------------------+| tbl       | id | its                        |+-----------+----+----------------------------+| it200212  |  1 | 2019-02-12 00:00:00.000000 || it200212  |  2 | 2019-02-13 00:00:00.000000 || it200212  |  3 | 2019-02-13 00:00:00.000000 || it200212a |  1 | 2019-02-12 00:00:00.000000 || it200212a |  2 | 2019-02-12 23:59:59.999999 || it200212a |  3 | 2019-02-13 00:00:00.000000 |+-----------+----+----------------------------+6 rows in set (0.00 sec)drop table it200212;drop table it200212a;-- 回复一般的型态SET sql_mode='';-- 接着来观察进位的情况create table it200212b (  id int unsigned not null auto_increment primary key, its timestamp not null);insert into it200212b(its) values(timestamp '2019-02-12 23:59:59.450000'),(timestamp '2019-02-12 23:59:59.459999'),(timestamp '2019-02-12 23:59:59.469999'),(timestamp '2019-02-12 23:59:59.499999'),(timestamp '2019-02-12 23:59:59.500000'),(timestamp '2019-02-12 23:59:59.510000'),(timestamp '2019-02-12 23:59:59.550000');select * from it200212b;+----+---------------------+| id | its                 |+----+---------------------+|  1 | 2019-02-12 23:59:59 ||  2 | 2019-02-12 23:59:59 ||  3 | 2019-02-12 23:59:59 ||  4 | 2019-02-12 23:59:59 ||  5 | 2019-02-13 00:00:00 ||  6 | 2019-02-13 00:00:00 ||  7 | 2019-02-13 00:00:00 |+----+---------------------+7 rows in set (0.00 sec)