Solved

Date/Time Difference in Oracle

Posted on 2009-04-02
6
3,215 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
[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
6 Comments
 
LVL 143

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 143

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 48

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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