How to retrive value for 12 hours

[v2naveen]

Hi,

I have table called CUSTOMER with the following fields and values

Interval_date rec_value
------------- ----------
10/1/2007 5:30:00 12.0
10/1/2007 6:00:00 17.0
10/1/2007 7:00:00 15.0
.
.
.
.
.
11/2/2007 5:30:00 18.0
11/2/2007 6:00:00 12.0
11/2/2007 6:30:00 21.0
11/2/2007 7:00:00 11.0

We have data in CUSTOMER table for every 30minutes.

My objective is to write a SQL query to retrieve the MININUM rec_value between 6:00:00PM(eveni ng) on a given date untill 6:00:00AM the following morning

ex: 11/1/2007 6:00:00PM to 12/1/2007 6:00:00AM.

I have tried following query but din't work.

select interval_date min(rec_value)
from CUSTOMER
WHERE interval_date BETWEEN TRUNC(interval_ date) +18/24 AND TRUNC(interval_ date) +6/24
GROUP BY interval_date;

Any clue as to how we can use date along with timestamp?

Your prompt reply is greatly appreciated.


Thanks & Regards
Naveen

[amitpatel66]

Hi,

I have table called CUSTOMER with the following fields and values

Interval_date rec_value
------------- ----------
10/1/2007 5:30:00 12.0
10/1/2007 6:00:00 17.0
10/1/2007 7:00:00 15.0
.
.
.
.
.
11/2/2007 5:30:00 18.0
11/2/2007 6:00:00 12.0
11/2/2007 6:30:00 21.0
11/2/2007 7:00:00 11.0

We have data in CUSTOMER table for every 30minutes.

My objective is to write a SQL query to retrieve the MININUM rec_value between 6:00:00PM(eveni ng) on a given date untill 6:00:00AM the following morning

ex: 11/1/2007 6:00:00PM to 12/1/2007 6:00:00AM.

I have tried following query but din't work.

select interval_date min(rec_value)
from CUSTOMER
WHERE interval_date BETWEEN TRUNC(interval_ date) +18/24 AND TRUNC(interval_ date) +6/24
GROUP BY interval_date;

Any clue as to how we can use date along with timestamp?

Your prompt reply is greatly appreciated.


Thanks & Regards
Naveen

Try this:

[code=oracle]

SELECT select interval_date min(rec_value)
from CUSTOMER
WHERE TO_CHAR(interva l_date,'DD/MM/YY HH12:MI:SS PM') BETWEEN TO_DATE('11/01/2007 06:00:00 PM','DD/MM/YYYY HH12:MI:SS PM') AND TO_DATE('11/02/2007 06:00:00 AM','DD/MM/YYYY HH12:MI:SS PM')

[/code]