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 
(
  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 test VALUES -- insert the same value 8 times into the different fields!
(
  '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899', 
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899'
);
SELECT 'ts',    's' AS dt, s  AS ts FROM test  UNION
SELECT 'ts(0)', 't',       t        FROM test  UNION
SELECT 'ts(1)', 'u',       u        FROM test  UNION
SELECT 'ts(2)', 'v',       v        FROM test  UNION
SELECT 'ts(3)', 'w',       w        FROM test  UNION
SELECT 'ts(4)', 'x',       x        FROM test  UNION
SELECT 'ts(5)', 'y',       y        FROM test  UNION
SELECT 'ts(6)', 'z',       z        FROM test;


ts
dt
ts


ts
s
2021-12-27 10:30:16.000000

ts(0)
t
2021-12-27 10:30:16.000000

ts(1)
u
2021-12-27 10:30:15.600000

ts(2)
v
2021-12-27 10:30:15.570000

ts(3)
w
2021-12-27 10:30:15.568000

ts(4)
x
2021-12-27 10:30:15.567900

ts(5)
y
2021-12-27 10:30:15.567900

ts(6)
z
2021-12-27 10:30:15.567899


--
-- Having satisfied ourselves that rounding rather than truncation is occurring in 
-- this case, we now check on how we might manage putting 6 digit precision timestamps
-- into a TIMESTAMP(3) field
--
-- First, we clear down the test table
--

DELETE FROM test;
SELECT * FROM test;


s
t
u
v
w
x
y
z


--
-- We create a test_bis table to hold a TIMESTAMP with a precision of 6
--

CREATE TABLE test_bis (ts TIMESTAMP(6));
INSERT INTO test_bis VALUES (TIMESTAMP('2021-12-27 10:30:15.567899'));
SELECT * FROM test_bis;


ts


2021-12-27 10:30:15.567899


INSERT INTO test
SELECT ts, ts, ts, ts, ts, ts, ts, ts FROM test_bis;

Records: 1  Duplicates: 0  Warnings: 0


SELECT 'ts',    's' AS dt, s  AS ts FROM test  UNION
SELECT 'ts(0)', 't'      , t        FROM test  UNION
SELECT 'ts(1)', 'u'      , u        FROM test  UNION
SELECT 'ts(2)', 'v'      , v        FROM test  UNION
SELECT 'ts(3)', 'w'      , w        FROM test  UNION
SELECT 'ts(4)', 'x'      , x        FROM test  UNION
SELECT 'ts(5)', 'y'      , y        FROM test  UNION
SELECT 'ts(6)', 'z'      , z        FROM test;

-- 
-- Again, we have rounding rather than truncation
--


ts
dt
ts


ts
s
2021-12-27 10:30:16.000000

ts(0)
t
2021-12-27 10:30:16.000000

ts(1)
u
2021-12-27 10:30:15.600000

ts(2)
v
2021-12-27 10:30:15.570000

ts(3)
w
2021-12-27 10:30:15.568000

ts(4)
x
2021-12-27 10:30:15.567900

ts(5)
y
2021-12-27 10:30:15.567900

ts(6)
z
2021-12-27 10:30:15.567899


SELECT 
  DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f')
FROM test_bis;


DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f')


2021-12-27 10:30:15.567899


CREATE TABLE var1 AS
SELECT 
  DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f')
FROM test_bis;

Records: 1  Duplicates: 0  Warnings: 0


DESCRIBE var1; 

--
-- Quite why this is VARCHAR(31) escapes me. Maybe it's something to do with
-- alignment padding of the records on disk or something that? Finding out
-- would require diving into the source code and is a bit above my pay grade (for
-- the moment at any rate!)
--


Field
Type
Null
Key
Default
Extra


DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f')
varchar(31)
YES

null



SELECT 
  SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23)
FROM test_bis;


SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23)


2021-12-27 10:30:15.567


--
-- Clear down test again
--

DELETE FROM test;
SELECT * FROM test;


s
t
u
v
w
x
y
z


SELECT 
  TIMESTAMP(SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23))
FROM test_bis;


TIMESTAMP(SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23))


2021-12-27 10:30:15.567000


CREATE TABLE var3 AS
SELECT 
  TIMESTAMP(SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23)) AS x
FROM test_bis;

Records: 1  Duplicates: 0  Warnings: 0


DESCRIBE var3;


Field
Type
Null
Key
Default
Extra


x
datetime(6)
YES

null



--
-- Clear down test again
--

DELETE FROM test;
SELECT * FROM test;


s
t
u
v
w
x
y
z


INSERT INTO test
SELECT x, x, x, x, x, x, x, x FROM var3;

Records: 1  Duplicates: 0  Warnings: 0


SELECT 'ts',    's' AS dt, s  AS ts FROM test  UNION
SELECT 'ts(0)', 't',       t        FROM test  UNION
SELECT 'ts(1)', 'u',       u        FROM test  UNION
SELECT 'ts(2)', 'v',       v        FROM test  UNION
SELECT 'ts(3)', 'w',       w        FROM test  UNION
SELECT 'ts(4)', 'x',       x        FROM test  UNION
SELECT 'ts(5)', 'y',       y        FROM test  UNION
SELECT 'ts(6)', 'z',       z        FROM test;

--
-- So, we see that we have truncation rather than rounding as desired!
--


ts
dt
ts


ts
s
2021-12-27 10:30:16.000000

ts(0)
t
2021-12-27 10:30:16.000000

ts(1)
u
2021-12-27 10:30:15.600000

ts(2)
v
2021-12-27 10:30:15.570000

ts(3)
w
2021-12-27 10:30:15.567000

ts(4)
x
2021-12-27 10:30:15.567000

ts(5)
y
2021-12-27 10:30:15.567000

ts(6)
z
2021-12-27 10:30:15.567000


SELECT 'ts'   , s AS ts, CHAR_LENGTH(s) AS len FROM test UNION ALL -- implicit STRING conversion!
SELECT 'ts(0)', t      , CHAR_LENGTH(t)        FROM test UNION ALL
SELECT 'ts(1)', u      , CHAR_LENGTH(u)        FROM test UNION ALL
SELECT 'ts(2)', v      , CHAR_LENGTH(v)        FROM test UNION ALL
SELECT 'ts(3)', w      , CHAR_LENGTH(w)        FROM test UNION ALL
SELECT 'ts(4)', x      , CHAR_LENGTH(x)        FROM test UNION ALL
SELECT 'ts(5)', y      , CHAR_LENGTH(y)        FROM test UNION ALL
SELECT 'ts(6)', z      , CHAR_LENGTH(z)        FROM test;


ts
ts
len


ts
2021-12-27 10:30:16.000000
19

ts(0)
2021-12-27 10:30:16.000000
19

ts(1)
2021-12-27 10:30:15.600000
21

ts(2)
2021-12-27 10:30:15.570000
22

ts(3)
2021-12-27 10:30:15.567000
23

ts(4)
2021-12-27 10:30:15.567000
24

ts(5)
2021-12-27 10:30:15.567000
25

ts(6)
2021-12-27 10:30:15.567000
26


--
--
-- 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()
2022-10-19 03:36:05.000000

NOW(0)
2022-10-19 03:36:05.000000

NOW(1)
2022-10-19 03:36:05.300000

NOW(2)
2022-10-19 03:36:05.350000

NOW(3)
2022-10-19 03:36:05.353000

NOW(4)
2022-10-19 03:36:05.353200

NOW(5)
2022-10-19 03:36:05.353260

NOW(6)
2022-10-19 03:36:05.353268


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
2022-10-19 03:36:05.000000

NOW(0)
t
2022-10-19 03:36:05.000000

NOW(1)
u
2022-10-19 03:36:05.300000

NOW(2)
v
2022-10-19 03:36:05.350000

NOW(3)
w
2022-10-19 03:36:05.356000

NOW(4)
x
2022-10-19 03:36:05.356100

NOW(5)
y
2022-10-19 03:36:05.356170

NOW(6)
z
2022-10-19 03:36:05.356179


SELECT
  table_name  AS "The table",
  data_length AS "Data length"
FROM
  information_schema.tables
WHERE
    TABLE_NAME = 'test'
ORDER BY
  1 + 2
DESC;


The table
Data length


test
16384


SHOW VARIABLES LIKE 'innodb_page_size';


Variable_name
Value


innodb_page_size
16384
ts	dt	ts
ts	s	2021-12-27 10:30:16.000000
ts(0)	t	2021-12-27 10:30:16.000000
ts(1)	u	2021-12-27 10:30:15.600000
ts(2)	v	2021-12-27 10:30:15.570000
ts(3)	w	2021-12-27 10:30:15.568000
ts(4)	x	2021-12-27 10:30:15.567900
ts(5)	y	2021-12-27 10:30:15.567900
ts(6)	z	2021-12-27 10:30:15.567899
NOW()	NOW()
NOW()	2022-10-19 03:36:05.000000
NOW(0)	2022-10-19 03:36:05.000000
NOW(1)	2022-10-19 03:36:05.300000
NOW(2)	2022-10-19 03:36:05.350000
NOW(3)	2022-10-19 03:36:05.353000
NOW(4)	2022-10-19 03:36:05.353200
NOW(5)	2022-10-19 03:36:05.353260
NOW(6)	2022-10-19 03:36:05.353268