oracle query between two dates with hours

Question

This is my current oracle table:

• DATE = date
• HOUR = number
• RUN_DURATION = number

I need a query to get RUN_DURATION between two dates with hours like

Select * from Datatable where DATE BETWEEN to_date('myStartDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS? and to_date('myEndDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS?

For example all data between 30.10.14 11:00:00 and 30.10.14 15:00:00

I stuck to get the hours to the dates. I tried to add the hours into myStartDate but this will be ignored for the start date because of BETWEEN. I know BETWEEN shouldn't be used for dates but I can't try other opportunities because I don't know how to get DATE and HOUR together... Thanks!

Iconoclast

There's absolutely nothing wrong with using BETWEEN with dates. Are you trying to add the HOUR column to the DATE column? Its very unclear. – Famous

The problem here is separating the dates from the hours. You'll have to combine them again to do the query, using something like (date + hour/24) – Duchy

@Nick.McDermaid I need for example all data for date between 10.08.15 08:00 and 11.08.15 10:00 – Glandulous

The dates in your example have no correlation to your screenshot. Please edit your question and explain what you would expect from the sample rows shown in your question. – Famous

@Nick.McDermaid the table has millions entries and some other fields. I made a query and updated the screenshot, hope now that it is clear. For example I need all data between 30.10.14 11:00:00 and 30.10.14 15:00:00, result should be entries with HOUR 12, 13 and 14 – Glandulous

If you want data between those dates... what rows in your example would you expect to return? – Famous

Answer 1

A DATE has both date and time elements. To add hours to date, you just need to do some mathematics.

For example,

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT SYSDATE, SYSDATE + 1/24 FROM dual;

SYSDATE             SYSDATE+1/24
------------------- -------------------
2015-08-14 15:02:59 2015-08-14 16:02:59

Adds 1 hour to sysdate. So, if you have a column of date data type, just add number of hours to be added divided by 24 because you add number of days to a date. So, you just need to first convert the number of hours into date as hours/24.

Equilibrant

Answer 2

Well, you can add hour to your field date this way

select "DATE" + (hour / 24) from <yourTable>

this will give you ( from your first sample, may be different based on your format)

August, 14 2015 10:00:00 
August, 14 2015 08:00:00 

Based on that, you can do any between, select that you need.

In your case

where "DATE" + (hour / 24 )

which would make

Select * 
from Datatable 
where "DATE" + (hour / 24 )
 BETWEEN to_date('30/10/2014 11:00:00', 'dd/mm/yyyy hh24:mi:ss')  and            
         to_date('30/10/2014 15:00:00', 'dd/mm/yyyy hh24:mi:ss') 

see SqlFiddle

(By the way, don't use reserved keywords for column name, but I guess this is just a sample).

Weidar

Thanks, works great. Thanks for the hint with the reserved keywords, it's a given table so I can't change the name. Made me some frustration with my c# query until someone told me to use \"DATE\" for the field – Glandulous

Answer 3

If you want to add min to date you can use interval.

select sysdate, sysdate + interval '1' hour from dual

So,

Select * 
from Datatable 
where 
DATE BETWEEN myStartDate + interval to_char(HOURS) hour 
and myEndDate + interval to_char(HOURS) hour

Seism

Answer 4

SELECT * 
FROM table 
WHERE date BETWEEN start_date +  NUMTODSINTERVAL( hour, 'HOUR' ) AND end_date +  NUMTODSINTERVAL( hour, 'HOUR' )

Cia