add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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