今天看到FB社群中有討論到 between, 進而有討論到關於精度的問題.
使用 MySQL 做了一些測試,與大家分享.

-- 使用 MySQL
select 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_fractional

insert 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 between

select *
  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)