CODEWITHSUNDEEP
sqlsplittimestamp
JazCodes
JazCodes

Reputation: 49

How to split a timestamp from 15 Minutes into 3 parts like every 5 minutes via SQL

I have a database which is really easy to handle normally. This dataset has a start_time, end_time, modellnumber, roomnumber. So I made a SQL which shows me a table how long each modell was in a room. But what I need is a table which shows me every 5 Minutes a new column, whether the modell was in this room. For better understanding :

First SQL: modell room start_time end_time

Second SQL: modell room duration_time

Needed SQL: modell room time

I wrote some different functions with trunc but nothing of them worked. Maybe someone have an idea with the examples I wrote down how to get the result I want. Please be kind, I am starting on this..

So this is my Second SQL for getting the duration time:

select(to_timestamp(end_time) - to_timestamp(start_time)) as duration_time, modell, room from objects

Upvotes: 0

Views: 276

Answers (1)

DannySlor
DannySlor

Reputation: 4620

Here's an example that you can mold to fit your needs. Let me know if you have any other questions.

with cal (col1, st, et) as 
(
               select col1, start_time as st, end_time as et  from t
    union all  select col1, st + interval '5' minute, et from cal where st <= et-interval '5' minute
)

select   *
from     cal 
order by col1, st
COL1 ST ET
1 31-OCT-22 10.05.00.000000000 31-OCT-22 10.17.00.000000
1 31-OCT-22 10.10.00.000000000 31-OCT-22 10.17.00.000000
1 31-OCT-22 10.15.00.000000000 31-OCT-22 10.17.00.000000
2 31-OCT-22 10.17.00.000000000 31-OCT-22 10.30.00.000000
2 31-OCT-22 10.22.00.000000000 31-OCT-22 10.30.00.000000
2 31-OCT-22 10.27.00.000000000 31-OCT-22 10.30.00.000000

Fiddle

Upvotes: 1

Related Questions