Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


-- Create the table
CREATE TABLE timestamps_table (
    id serial PRIMARY KEY,
    timestamp_value timestamp
);

CREATE TABLE


-- Insert sample data
INSERT INTO timestamps_table (timestamp_value) VALUES
    ('2023-07-01 10:00:00'),
    ('2023-07-01 10:10:01'),
    ('2023-07-01 10:01:00'),
    ('2023-07-01 10:05:00'),
    ('2023-07-01 10:09:59'),
    ('2023-07-01 10:19:59'),
    ('2023-07-01 10:49:09'),
    ('2023-07-01 10:59:59');

INSERT 0 8


-- Select and round timestamps
SELECT    
     timestamp_value,
    (date_trunc('hour', timestamp_value) +
    CEIL(EXTRACT(MINUTE FROM timestamp_value) / 10) * INTERVAL '10 minutes') AS rounded_timestamp_value
FROM timestamps_table;




timestamp_value
rounded_timestamp_value


2023-07-01 10:00:00
2023-07-01 10:00:00

2023-07-01 10:10:01
2023-07-01 10:10:00

2023-07-01 10:01:00
2023-07-01 10:10:00

2023-07-01 10:05:00
2023-07-01 10:10:00

2023-07-01 10:09:59
2023-07-01 10:10:00

2023-07-01 10:19:59
2023-07-01 10:20:00

2023-07-01 10:49:09
2023-07-01 10:50:00

2023-07-01 10:59:59
2023-07-01 11:00:00



SELECT 8

timestamp_value	rounded_timestamp_value
2023-07-01 10:00:00	2023-07-01 10:00:00
2023-07-01 10:10:01	2023-07-01 10:10:00
2023-07-01 10:01:00	2023-07-01 10:10:00
2023-07-01 10:05:00	2023-07-01 10:10:00
2023-07-01 10:09:59	2023-07-01 10:10:00
2023-07-01 10:19:59	2023-07-01 10:20:00
2023-07-01 10:49:09	2023-07-01 10:50:00
2023-07-01 10:59:59	2023-07-01 11:00:00