Postgres 15

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

CREATE TABLE tbl (
  ts       timestamp
, expected timestamp
);

INSERT INTO tbl VALUES
  ('2023-07-01 10:00:00')
, ('2023-07-01 10:00:01')
, ('2023-07-01 10:01:00')
, ('2023-07-01 10:05:00')
, ('2023-07-01 10:09:59')
, ('2023-07-01 10:10:00')
;

CREATE TABLE



INSERT 0 6


-- date_bin(), then increment by 10
SELECT ts
     , date_bin('10 min', ts, '2000-1-1') + interval '10 min' AS next_10
FROM   tbl;


ts
next_10


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

2023-07-01 10:00: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:10:00
2023-07-01 10:20:00



SELECT 6


-- rounding up correctly
SELECT ts
     , CASE WHEN date_bin('10 min', ts, '2000-1-1') = ts THEN ts ELSE date_bin('10 min', ts, '2000-1-1') + interval '10 min' END AS round_10
     , COALESCE(NULLIF(date_bin('10 min', ts, '2000-1-1'), ts) + interval '10 min', ts) AS faster_10
FROM   tbl;


ts
round_10
faster_10


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

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

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

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

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

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



SELECT 6

ts	next_10
2023-07-01 10:00:00	2023-07-01 10:10:00
2023-07-01 10:00: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:10:00	2023-07-01 10:20:00