?
Solved

Selecting data via a time calculation

Posted on 2004-09-14
7
Medium Priority
?
527 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 400 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 400 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

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!

Question has a verified solution.

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

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…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

752 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