Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle categorizing the timestamp column into 15 minute bucket

I am working on writing a SQL query to categorize one of my timestamp column to 15 minutes bucket. I did some research and tried multiple methods but I am not getting the desired result.

SELECT dd,
       TRUNC(dd) + ROUND(TO_CHAR(dd, 'SS') / 900) / 96 AS round_1,
       TRUNC(dd, 'hh24') + ROUND(TO_CHAR(dd, 'MI') / 15) * 15 / 1440 AS round_2,
       TRUNC(dd, 'mi') -
       MOD(EXTRACT(minute FROM CAST(dd AS timestamp)), 15) / (24 * 60) AS round_3,
       TO_CHAR(TRUNC(dd, 'hh') +
               (15 * ROUND(TO_CHAR(TRUNC(dd, 'MI'), 'MI') / 15)) / 1440,
               'YYYY-MM-DD hh24:mi:ss') AS round_4
  FROM Table

When I execute the above queries, I see the following results.

enter image description here

The expected result is
enter image description here

***Edits, Updated the Question based on Gordon reply

like image 716
Nik Avatar asked Dec 06 '25 18:12

Nik


1 Answers

You can use TRUNC() function for each addend of the below addition as an option :

SELECT dd,
       TRUNC(dd,'HH24')+
       TRUNC(TO_CHAR(dd,'MI')/15)*INTERVAL '15' MINUTE
  FROM tab 

The Demo in order to display the result through TO_CHAR() function.

like image 74
Barbaros Özhan Avatar answered Dec 08 '25 09:12

Barbaros Özhan



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!