By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.7.39 |
CREATE TABLE test
(
x TIMESTAMP(0) NULL DEFAULT NULL,
y TIMESTAMP(3) NULL DEFAULT NULL,
z TIMESTAMP(6) NULL DEFAULT NULL
);
INSERT INTO test VALUES (NOW(), NOW(), NOW());
SELECT * FROM test;
-- with MySQL 8, the precision value is always truncated to .000 or .000000?
-- PostgreSQL appears to work as expected.
-- MySQL 5.7 won't work at all!
| x | y | z |
|---|---|---|
| 2024-08-22 19:20:12 | 2024-08-22 19:20:12.000 | 2024-08-22 19:20:12.000000 |
INSERT INTO test VALUES (NOW(0), NOW(3), NOW(6));
INSERT INTO test VALUES (NOW(6), NOW(6), NOW(6));
INSERT INTO test VALUES (NOW(5), NOW(5), NOW(5));
SELECT * FROM test;
| x | y | z |
|---|---|---|
| 2024-08-22 19:20:12 | 2024-08-22 19:20:12.000 | 2024-08-22 19:20:12.000000 |
| 2024-08-22 19:20:12 | 2024-08-22 19:20:12.147 | 2024-08-22 19:20:12.147382 |
| 2024-08-22 19:20:12 | 2024-08-22 19:20:12.148 | 2024-08-22 19:20:12.147689 |
| 2024-08-22 19:20:12 | 2024-08-22 19:20:12.148 | 2024-08-22 19:20:12.147910 |