Solved

Date/Time Difference in Oracle

Posted on 2009-04-02
6
3,209 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

Technology Partners: 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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

730 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