Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date/Time Difference in Oracle

Posted on 2009-04-02
6
Medium Priority
?
3,239 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
5 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 252 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 252 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

810 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