How to bin timestamp data into buckets of custom width of n hours in vertica

I have a table which contains a column Start_Timestamp which has time stamp values like 2020-06-02 21:08:37. I would like to create new column which classifies these timestamps into bins of 6hours.

Eg.

Input :

Start_Timestamp
2020-06-02 21:08:37
2020-07-19 01:23:40
2021-11-13 12:08:37

Expected Output ( Here each bin is of 6hours width) :

Start_Timestamp	Bin
2020-06-02 21:08:37	18H - 24H
2020-07-19 01:23:40	00H - 06H
2021-11-13 12:08:37	12H - 18H

I have tried using TIMESERIES but can anyone help to generate output in following format

Solution

It's Vertica. Use the TIME_SLICE() function. Then, combine it with the TO_CHAR() function that Vertica shares with Oracle.

You can always add a CASE WHEN expression to change 00:00 to 24:00, but as that is not the standard, I wouldn't even bother.

WITH
indata(start_ts) AS (
          SELECT TIMESTAMP '2020-06-02 21:08:37'
UNION ALL SELECT TIMESTAMP '2020-07-19 01:23:40'
UNION ALL SELECT TIMESTAMP '2021-11-13 12:08:37'
)
SELECT 
  TIME_SLICE(start_ts,6,'HOUR') 
  AS tm_slice
,   TO_CHAR(TIME_SLICE(start_ts,6,'HOUR'),'HH24:MIH - ')
  ||TO_CHAR(TIME_SLICE(start_ts,6,'HOUR','END'),'HH24:MIH') 
  AS caption
, start_ts
FROM indata;
-- out       tm_slice       |     caption     |      start_ts       
-- out ---------------------+-----------------+---------------------
-- out  2020-06-02 18:00:00 | 18:00H - 00:00H | 2020-06-02 21:08:37
-- out  2020-07-19 00:00:00 | 00:00H - 06:00H | 2020-07-19 01:23:40
-- out  2021-11-13 12:00:00 | 12:00H - 18:00H | 2021-11-13 12:08:37