CODEWITHSUNDEEP
sqloracle-database
Thej Kumar
Thej Kumar

Reputation: 91

Find the minute difference between 2 date time

I need to get the difference between 2 date time in minutes(Time difference in minutes). And the last difference will be calculated based on 6 PM of every date.

Sample data: need result of last column

User_Name   Date            Time difference in minutes
User 1  1/1/06 12:00 PM     30
user 2  1/1/06 12:30 PM     315
user 3  1/1/06 5:45 PM      15

Here the date will be always in same date and the last user date difference calculated based on default value 6PM. Assuming the dates of any user will not cross 6PM time.

Please suggest how to write the query for the same.

Upvotes: 0

Views: 79

Answers (2)

trincot
trincot

Reputation: 350272

You could use the lead window function.

I assume your table is called mytable and the date column is mydate (it is a bad idea to call a column Date as it is a reserved word).

select   user_name,
         round((lead(mydate, 1, trunc(mydate)+18/24) 
             over (partition by trunc(mydate) order by mydate) 
         - mydate) *24*60) as difference 
from     mytable

Upvotes: 2

Thej Kumar
Thej Kumar

Reputation: 91

I found the solution.. if its not correct let me know

SELECT User_name,created_date,
trunc(to_number((cast(nvl(lead (created_date,1) OVER (ORDER BY created_date),TRUNC(SYSDATE) + (19/24)) as date) - cast(created_date as date)))*24*60) as difference
FROM users;

Upvotes: 0

Related Questions