MySQL 5.7

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-03-14 17:43:23
2024-03-14 17:43:23.000
2024-03-14 17:43:23.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-03-14 17:43:23
2024-03-14 17:43:23.000
2024-03-14 17:43:23.000000

2024-03-14 17:43:23
2024-03-14 17:43:23.751
2024-03-14 17:43:23.751951

2024-03-14 17:43:24
2024-03-14 17:43:23.752
2024-03-14 17:43:23.752188

2024-03-14 17:43:24
2024-03-14 17:43:23.752
2024-03-14 17:43:23.752390

x	y	z
2024-03-14 17:43:23	2024-03-14 17:43:23.000	2024-03-14 17:43:23.000000
2024-03-14 17:43:23	2024-03-14 17:43:23.751	2024-03-14 17:43:23.751951
2024-03-14 17:43:24	2024-03-14 17:43:23.752	2024-03-14 17:43:23.752188
2024-03-14 17:43:24	2024-03-14 17:43:23.752	2024-03-14 17:43:23.752390