Solved

Selecting data via a time calculation

Posted on 2004-09-14
7
521 Views
Last Modified: 2013-12-11
I searched and found a few solutions close to what I'm looking for, but nothing got me over the hump.

My SQL statement follows:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where to_char(transaction0.document_date, 'mmddyy') = to_char(sysdate, 'mmddyy') and
log_field4 = 'O'
order by log_field3 desc

In english pseudocode, here is essentially what i'm looking for.

where log_field3 >= (Current Time - 60)
(All records from the past 60 minutes)

The time field (log_field3) follows the format: 0800, 1200, etc. So I believe its in the correct format to be compared to the systime.

Any help would be appreciated.
Thanks!
Kevin
0
Comment
Question by:kevinjd
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 100 total points
ID: 12061740
How about:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where log_field4 = 'O'
and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
order by log_field3 desc;


0
 

Author Comment

by:kevinjd
ID: 12063597
I tried that, this was my result...

SQL> select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
  2  from transaction0
  3  where log_field4 = 'O'
  4  and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
  5  order by log_field3 desc;
and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
                                                *
ERROR at line 4:
ORA-01840: input value not long enough for date format
0
 
LVL 1

Assisted Solution

by:Jankorat
Jankorat earned 100 total points
ID: 12063643

The question is not clear but from what i understand you have table transaction0
with fields document_date with date(assumption) format and the time part is stored in another field log_field3 of type varchar2 . It is always better to store date in the date field including the time part for performance and for logic .

If this is  your situtation you could do the following

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where transaction0.document_date+substr( log_field3,1,2)/24+substr( log_field3,3,2)/1440   between sysdate  and sysdate-60/1440
and log_field4 = 'O'
order by log_field3 desc


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:cjjclifford
ID: 12063920
ishando's soln. should work...
can you post the output of:

DESCRIBE tranaction0

Also, if the field log_field3 can be NULL then ishando's answer may produce incorrect strings to be passed to the outer TO_DATE
This is because Oracle handles string concatentation of string and NULL in an "unexpected" way, returning the string, not NULL, e.g.
SELECT 'a' || null from dual;

so, if LOG_FIELD3 can be NULL, then the string being passed to the outer TO_DATE will not be formatted correctly....

to get around this, you need to wrap part of it in a DECODE:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where log_field4 = 'O'
and to_date(DECODE( log_field3, null, null, to_char(document_date, 'yyyymmdd') ) || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
order by log_field3 desc;

Cheers,
C.
0
 

Author Comment

by:kevinjd
ID: 12065548
Jankorat,

What i'm looking for is all the records in which the time stored in log_field3 is within the past 60 minutes.
I'm currently working on testing your sql as well as cjjclifford.
0
 
LVL 15

Expert Comment

by:ishando
ID: 12070499
what datatype is the document_date?
if it is a date it should work. If not then it won't.
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12072908
ishando, apart from when log_field3 is null, which would result in an incorrectly formatted string being passed to the outer TO_DATE()...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now