By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.7.35 |
--
--
-- Test of NOW(p) at various values of p - checking if rounding or truncation
-- occurs
--
SELECT 'NOW()' , NOW() UNION ALL
SELECT 'NOW(0)', NOW(0) UNION ALL
SELECT 'NOW(1)', NOW(1) UNION ALL
SELECT 'NOW(2)', NOW(2) UNION ALL
SELECT 'NOW(3)', NOW(3) UNION ALL
SELECT 'NOW(4)', NOW(4) UNION ALL
SELECT 'NOW(5)', NOW(5) UNION ALL
SELECT 'NOW(6)', NOW(6);
--
-- A few runs of the fiddle clearly shows that the value is truncated and not
-- rounded, to using NOW(3) for millisecond precision will not risk
-- chronological anomalies!
--
| NOW() | NOW() |
|---|---|
| NOW() | 2021-12-28 10:27:55.000000 |
| NOW(0) | 2021-12-28 10:27:55.000000 |
| NOW(1) | 2021-12-28 10:27:55.800000 |
| NOW(2) | 2021-12-28 10:27:55.880000 |
| NOW(3) | 2021-12-28 10:27:55.884000 |
| NOW(4) | 2021-12-28 10:27:55.884200 |
| NOW(5) | 2021-12-28 10:27:55.884210 |
| NOW(6) | 2021-12-28 10:27:55.884212 |
CREATE TABLE now_test
(
s TIMESTAMP,
t TIMESTAMP(0) NULL DEFAULT NULL,
u TIMESTAMP(1) NULL DEFAULT NULL,
v TIMESTAMP(2) NULL DEFAULT NULL,
w TIMESTAMP(3) NULL DEFAULT NULL,
x TIMESTAMP(4) NULL DEFAULT NULL,
y TIMESTAMP(5) NULL DEFAULT NULL,
z TIMESTAMP(6) NULL DEFAULT NULL
);
INSERT INTO now_test VALUES
(NOW(), NOW(0), NOW(1), NOW(2), NOW(3), NOW(4), NOW(5), NOW(6));
SELECT 'NOW()' , 's' AS dt, s AS ts FROM now_test UNION
SELECT 'NOW(0)', 't' , t FROM now_test UNION
SELECT 'NOW(1)', 'u' , u FROM now_test UNION
SELECT 'NOW(2)', 'v' , v FROM now_test UNION
SELECT 'NOW(3)', 'w' , w FROM now_test UNION
SELECT 'NOW(4)', 'x' , x FROM now_test UNION
SELECT 'NOW(5)', 'y' , y FROM now_test UNION
SELECT 'NOW(6)', 'z' , z FROM now_test;
--
-- As with the SELECTs above, a few runs of the fiddle clearly show that the
-- value is truncated at 3 digits for the 'w' (TIMESTAMP(3)) field
--
| NOW() | dt | ts |
|---|---|---|
| NOW() | s | 2021-12-28 10:27:55.000000 |
| NOW(0) | t | 2021-12-28 10:27:55.000000 |
| NOW(1) | u | 2021-12-28 10:27:55.900000 |
| NOW(2) | v | 2021-12-28 10:27:55.900000 |
| NOW(3) | w | 2021-12-28 10:27:55.902000 |
| NOW(4) | x | 2021-12-28 10:27:55.902000 |
| NOW(5) | y | 2021-12-28 10:27:55.902070 |
| NOW(6) | z | 2021-12-28 10:27:55.902073 |
SELECT
table_name AS "The table",
data_length AS "Data length"
FROM
information_schema.tables
WHERE
TABLE_NAME = 'now_test'
ORDER BY
1 + 2
DESC;
| The table | Data length |
|---|---|
| now_test | 16384 |
SHOW VARIABLES LIKE 'innodb_page_size';
| Variable_name | Value |
|---|---|
| innodb_page_size | 16384 |