Solved

Date/Time Difference in Oracle

Posted on 2009-04-02
6
3,201 Views
Last Modified: 2013-12-18
I have one problem left with my sql when calculating a time difference.  Assume EndDate = 03/31/2009 02:00:47 and BeginDate is  03/31/2009 02:00:46.  I should get 1 second.  I am getting 00:00:00.  Everything above 1 second works just great.  This is what I'm using:

TO_CHAR(TO_DATE(TRUNC(MOD(enddate-begindate,1)*86400),'SSSSS'), 'HH24:MI:SS')
0
Comment
Question by:marfi95
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24055512
question: are the date/time values you showed for enddate and begindate really exactly that, or do they eventually have some milliseconds?
0
 

Author Comment

by:marfi95
ID: 24055540
they are exactly that.  the fields are DATE type in the database
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24055673
can you should TO_CHAR(begindate, 'HH:MI:SS.NNN') and TO_CHAR(enddate, 'HH:MI:SS.NNN'), please?
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 63 total points
ID: 24055884
Its because of the order of your arithmetic. Oracle subtracts 2 date values and returns a floating point value that represents days.  enddate - begindate for 1 second different is really: .00001157

You then are converting it to seconds, but as a floating point value,  you get rounding errors, and its less than thje integer 1 (actually 0.999 something), so when passing that to TO_DATE( ..., 'SSSSS') Oracle isn't going to round UP, its just goint to give you the "SECOND" component, which is 0.

Instead, convert the values into seconds _before_ doing the arithmetic on them.

select TO_NUMBER(TO_CHAR(enddate, 'SSSSS'))

     - TO_NUMBER(TO_CHAR(begindate, 'SSSSS'))

from MYTABLE
 

Add see what you get.

Open in new window

0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 63 total points
ID: 24059248
Excuse me, what was your question?
SQL> SELECT TO_DATE('03/31/2009 02:00:47', 'mm/dd/yyyy hh:mi:ss') -  TO_DATE('03/31/2009 02:00:46','

mm/dd/yyyy hh:mi:ss') as dif FROM dual;
 

       DIF

----------

.000011574
 

SQL> SELECT (TO_DATE('03/31/2009 02:00:47', 'mm/dd/yyyy hh:mi:ss') -  TO_DATE('03/31/2009 02:00:46',

'

  2  mm/dd/yyyy hh:mi:ss')) *24*68*60 as dif FROM dual;
 

       DIF

----------

1.13333333

Open in new window

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.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 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

8 Experts available now in Live!

Get 1:1 Help Now