Home » SQL & PL/SQL » SQL & PL/SQL » HOW to retrieve values for 12 hours (Unix)
HOW to retrieve values for 12 hours [message #300274] Thu, 14 February 2008 19:49 Go to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
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(evening) 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





Re: HOW to retrieve values for 12 hours [message #300305 is a reply to message #300274] Thu, 14 February 2008 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Date in Oracle include time.
As your BETWEEN expression contain a first expression greater than the second one, the query will never return a row.

Do you want a result for each 12 hours period or a result for the times you gave for a specific day?
Do you just want the min value, or do you this value along with interval it happens?

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: HOW to retrieve values for 12 hours [message #300349 is a reply to message #300274] Fri, 15 February 2008 02:46 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

You can try with

Quote:

select * from table_name where to_char(given_date,'hh24')>=18
and to_char(give_date+1,'hh24')<=6;


I hope it will be helpful for you.


Thanks
Mano



Re: HOW to retrieve values for 12 hours [message #300355 is a reply to message #300349] Fri, 15 February 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope it will be helpful for you.

No, it is wrong in many ways.
And the first one is to compare strings and numbers.

Regards
Michel
Re: HOW to retrieve values for 12 hours [message #300488 is a reply to message #300305] Fri, 15 February 2008 11:49 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
I need a query which will give me the minimum value for for 12 hour window. It should give the minimum of VALUE column for the interval_startdt column from 6:00PM to next day 6:00AM.

For ex: I need MIN(VALUE) where INTERVAl_STARDT between
1/1/2008 6:00:00 PM AND 1/2/2008 6:00:00 AM.

The result should look like this.

INTERVAL_STARTDT MIN(VALUE)
---------------- ----------
11/1/2008 12.7
11/2/2008 13.5
11/3/2008 8.6

WHERE the 12.7 value for 11/1/2008 accounts the data for 12 hour window.

Hope this is clear.

Thanks
Re: HOW to retrieve values for 12 hours [message #300489 is a reply to message #300349] Fri, 15 February 2008 11:50 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Sorry the query din't return any values.
Re: HOW to retrieve values for 12 hours [message #300491 is a reply to message #300488] Fri, 15 February 2008 11:54 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your post is inconsistent. Clarify. If this is "between 1/1/2008 6:00:00 PM AND 1/2/2008 6:00:00 AM", why there are results for 11/1, 11/2 and 11/3?

Reply PRECISELY to my questions.
Follow the guide.

Post a test case: create table and insert statements.
Also post the result you want with these data.

Regards
Michel

Previous Topic: string function or utility help
Next Topic: Round Function
Goto Forum:
  


Current Time: Sun Dec 04 04:50:28 CST 2016

Total time taken to generate the page: 0.04148 seconds