Oracle categorizing the timestamp column into 15 minute bucket

user_10382395 0
Viewed 174

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

oracle sql
2 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.

user_58413060

Oracle doesn't have a datetime data type. If the column is a timestamp (as your code suggests), then you can use this logic:

select trunc(current_timestamp, 'HH24') + floor(extract(minute from current_timestamp) / 4) * interval '15' minute
from dual
user_11440350
Related