Is there a way to round a timestamp up or down to the nearest 30 minute interval?

Question

I'm currently using Presto and I was wondering how I could possibly round UP or DOWN a timestamp to the nearest 30 minute interval?

This is what my timestamp looks like: 2021-08-23 17:46:06

This is what I would like this to look like: 2021-08-23 18:00

Thanks so much in advance!

Answer 1

To round up - take processed date, add to it 30 minutes minus precision (1 minute in case of minutes, 1 second in case of seconds and so on), then take some "base" date ('2021-01-01' in case of my test data), find difference in minutes then divide by 30 and multiply by 30 and add the result back to the base date basically rounding down the modified date. Rounding down will work the same way without adding 30 minutes:

WITH dataset(time) AS (
   VALUES 
  (timestamp '2021-08-23 17:29:59'),
  (timestamp '2021-08-23 17:30:00'),
  (timestamp '2021-08-23 17:30:01'),
  (timestamp '2021-08-23 17:31:01'),
  (timestamp '2021-08-23 17:59:59'),
  (timestamp '2021-08-23 18:00:00'),
  (timestamp '2021-08-23 18:00:01')
 ) 
 
SELECT date_add(
  'minute', 
  date_diff(
    'minute',
    timestamp '2021-01-01', -- "base" date
    (time + interval '30' minute - interval '1' second) -- remove the +/- interval parts to round down
  ) / 30 * 30, 
  timestamp '2021-01-01') -- "base" date
FROM dataset

Output:

_col0
2021-08-23 17:30:00.000
2021-08-23 17:30:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:30:00.000