Solved

Selecting data via a time calculation

Posted on 2004-09-14
7
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

724 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