CODEWITHSUNDEEP
sqloracle-database
user1468031
user1468031

Reputation: 39

how to query Oracle column of Date with timestamp

I have a table 1 with below structure:

workflow_name varchar2(50)
session_name  varchar2(50)
actual_start  Date

TableData look like below:

workflow1  session1  21-JAN-16 21:30:49

now i want to run query at 30min. if i am running at 10am means i want to find out how many sessions run from 9.30am to 9.59am

i will write my query

select count(*) from table1
where workflowname ='workflow1'
and sessionname ='sessionname1'
and actual_start should between 9.30am to 9.59am

how do i code in for actual_start should between 9.30am to 9.59am?

thanks

Upvotes: 0

Views: 879

Answers (1)

Michael Broughton
Michael Broughton

Reputation: 4055

EDIT: Fixed error in endpoint calculations!

At any given moment in time, this will give the most recently passed half hour interval:

select case when sysdate - trunc(sysdate,'HH') > (30/1440) then trunc(sysdate,'HH') 
        else trunc(sysdate,'HH')- (30/1440) end as start_point
      ,case when sysdate - trunc(sysdate,'HH') > (30/1440) then trunc(sysdate,'HH') + (30/1440)
        else trunc(sysdate,'HH') end as end_point  
from dual

so at 10:01 it will give today's 9:30 - 10:00, and at 10:40 it will give today's 10:00 - 10:30.

So you just need to look for data in that range (You will need to figure out where the end-points fall as one end needs to be inclusive and the other exclusive to avoid a row being counted twice. I made the lower bound inclusive in my example):

SELECT * 
FROM table1
WHERE actual_start_date >= (select case when sysdate - trunc(sysdate,'HH') > (30/1440) then trunc(sysdate,'HH') 
                             else trunc(sysdate,'HH')- (30/1440) end
                            FROM DUAL)
AND actual_start_date < (SELECT case when sysdate - trunc(sysdate,'HH') > (30/1440) then trunc(sysdate,'HH') + (30/1440) 
                           else trunc(sysdate,'HH') end 
                          from dual)

And now you will get the most recently proceeding half hour whenever you run it.

If you want the REALLY simple query - just get rows for the last 30 minutes from RIGHT NOW, then just:

SELECT * 
FROM table1
WHERE actual_start_date >= (sysdate - (30/1440));

Upvotes: 1

Related Questions