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

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