1

I get some data through a OSB Proxy Service and have to transform that using Xquery. Earlier the transformation was done on the database but now it is to be done on the proxy itself. So I have been given the SQL queries which were used and have to generate Xquery expressions corresponding to those.

Here is the SQL query which is supposed to find the difference between 2 dates.

SELECT ROUND((CAST(DATEATTRIBUTE2 AS DATE) -
              CAST(DATEATTRIBUTE1 AS DATE) ) * 86400 ) AS result
FROM SONY_TEST_TABLE;

DATEATTRIBUTE1 and DATEATTRIBUTE2 are both of TIMESTAMP type.
As per my understanding this query first casts the TIMESTAMP to DATE so that the time part is stripped then subtracts the dates. That difference in days in multiplied with 86400 to get the duration in seconds.

However, when I take DATEATTRIBUTE2 as 23-02-17 01:17:19.399000000 AM and DATEATTRIBUTE1 as 23-02-17 01:17:18.755000000 AM the result should ideally be 0 as the dates are same and i'm ignoring the time difference but surprisingly the result comes as 1. After checking I found that the ( CAST(DATEATTRIBUTE2 AS DATE) - CAST(DATEATTRIBUTE1 AS DATE) ) part aparently does not give an integer value but a fractional one.

How does this work?? o_O

Any help is appreciated. Cheers!

EDIT : So got the problem thanks to all the answers! Even after casting to DATE it still has time so the time difference is also calculated. Now how do I implement this in XQuery? See this other question.

Community
  • 1
  • 1
boredDev
  • 317
  • 3
  • 18
  • The difference in seconds is not zero. What output do you actually want here? – Tim Biegeleisen Feb 24 '17 at 07:42
  • Possible duplicate of [Calculate difference between 2 date / times in Oracle SQL](http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql) – Tim Biegeleisen Feb 24 '17 at 07:44
  • " I can't make any changes to the SQL. I have to replicate the same results using Xquery " Please explain this. You are running XQUERY against a database table? Or some different data source? – APC Feb 24 '17 at 08:26
  • @TimBiegeleisen Hi.. I wanted to understand the SQL query first as I have to develop an Xquery corresponding to this SQL. My Xquery is returning different result than the SQL in cases where the dates are same but time is different. I need help with the *Xquery* to give same output as the given *SQL* – boredDev Feb 24 '17 at 09:16
  • @APC I get some data through a OSB Proxy Service and have to transform that using Xquery. Earlier the transformation was done on the database but now it is to be done on the proxy itself. So I have been given the SQL queries which were used and have to generate Xquery expressions corresponding to those. – boredDev Feb 24 '17 at 09:19
  • 1
    "*so that the time part is stripped*" - not it's not. In Oracle a `DATE` still has a time. –  Feb 24 '17 at 09:23
  • 1
    I would suggest linking to the new question that you rightfully opened here http://stackoverflow.com/questions/42437883/what-would-be-the-xquery-expression-to-count-the-number-of-seconds-between-two-d That way, the question here focuses on SQL while the other on XQuery. – Ghislain Fourny Feb 24 '17 at 16:19
  • @GhislainFourny Thanks! how do I do that ? – boredDev Feb 27 '17 at 09:50
  • 1
    I have just edited the question accordingly, reformulating the last sentence with a link. I hope this helps. – Ghislain Fourny Mar 01 '17 at 15:04

2 Answers2

2

you should try this to find difference by day

SELECT  (trunc(DATEATTRIBUTE2) -
              trunc(DATEATTRIBUTE1) )  AS result
FROM SONY_TEST_TABLE;

alternative 2

you can use extract like below:

SELECT ROUND (
            EXTRACT (MINUTE FROM INTERVAL_DIFFERENCE) / (24 * 60)
          + EXTRACT (HOUR FROM INTERVAL_DIFFERENCE) / 24
          + EXTRACT (DAY FROM INTERVAL_DIFFERENCE))
  FROM (SELECT (  TO_TIMESTAMP ('23-02-17 01:17:19', 'dd-mm-yy hh24:mi:ss')
                - TO_TIMESTAMP ('23-02-17 01:17:17', 'dd-mm-yy hh24:mi:ss'))
                  INTERVAL_DIFFERENCE
          FROM DUAL)
CompEng
  • 7,161
  • 16
  • 68
  • 122
2

Oracle DATE datatype is actually a datetime. So casting something as a date doesn't remove the time element. To do that we need to truncate the value:

( trunc(DATEATTRIBUTE2) - trunc(DATEATTRIBUTE1) )
APC
  • 144,005
  • 19
  • 170
  • 281
  • @ErsinGülbahar - We typed our answers simultaneously. – APC Feb 24 '17 at 07:44
  • @TimBiegeleisen - actually that answer doesn't include TRUNC() to remove the time element, so it isn't a duplicate. But probably there are other answers which do. – APC Feb 24 '17 at 07:48
  • Okay so DATE and DATETIME are similar to TIMESTAMP and both contain the time part? And I can't make any changes to the SQL. I have to replicate the same results using Xquery – boredDev Feb 24 '17 at 07:53
  • @APC The difference between `01-MAR-17 01.34.56.380000000 PM` and `01-MAR-17 01.34.56.827000000 PM` comes as 0 when I use the query that I posted in the question (removing the ROUND() function) but there should be some positive value there as the time part is also preserved and there is a time difference in milliseconds. right? – boredDev Mar 01 '17 at 19:40