?
Solved

Date/Time Difference in Oracle

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

765 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