vote up 1 vote down
star

Hi Experts,

Need help here. In Oracle when we do timestamp difference. The resultset would be days and with some decimal numbers. For Eg.

SELECT '03.12.2004:10:34:24' "Now",
       TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
       - hiredate "Hired since [Days]"
  FROM emp;
Now                 Hiredate            Hired since [Days]
------------------- ------------------- ------------------
03.12.2004:10:34:24 17.12.1980:00:00:00         8752.44056

In the above example, we get 8752.44056 as answer. In that, '8752' is no. of days and want to know what is '44056' the precision values,

I want to do the same difference in teradata, I am able to get the no. of days but do not know how to get the precision values. I need the same format in teradata. Please help me in this. Thanks in advance.

Regards, TDHelp

flag

1 Answer

vote up 1 vote down 
SELECT (CURRENT_TIMESTAMP(0) - 
        TIMESTAMP '2010-01-01 18:20:10') DAY(4) TO SECOND(4) AS MYINTERVAL

The output will look something like 20 04:22:30.00 which is days hh24:mi:ss.ff. DAY(4) TO SECOND(4) will cover roughly 27 years or so.

If the interval result is not appropriate, then if you extend the example I gave here Timestamp diff with HOUR(4) TO SECOND(4) as follows:

SELECT END_TIMESTAMP
     , START_TIMESTAMP
     , (INTERVALDAYS * 86400) /* SECONDS IN A DAY */
     + (INTERVALHOURS * 3600) /* SECONDS IN A HOUR */
     + (INTERVALMINUTES * 60) /* SECONDS IN A MINUTE */
     + INTERVALSECONDS          AS SECONDS_ELAPSED
     , SECONDS_ELAPSED / 86400.0000 AS DAYS_BETWEEN
FROM (
SELECT TIMESTAMP '2006-11-20 00:00:00' AS END_TIMESTAMP
     , TIMESTAMP '2006-10-28 03:36:03' AS START_TIMESTAMP
     , (END_TIMESTAMP - START_TIMESTAMP) DAY(4) TO SECOND(4) AS MYINTERVAL
     , EXTRACT(DAY FROM MYINTERVAL) AS INTERVALDAYS
     , EXTRACT(HOUR FROM MYINTERVAL) AS INTERVALHOURS
     , EXTRACT(MINUTE FROM MYINTERVAL) AS INTERVALMINUTES
     , EXTRACT(SECOND FROM MYINTERVAL) AS INTERVALSECONDS
     ) DT1

You will find that the DAYS_BETWEEN value matches what you are getting from Oracle. Teradata Timestamp math is obviously cumbersome, but it is what it is and this is how you work with it.

link|flag
My requirement is, I should display same as in Oracle: In Oracle,select ROUND(TO_DATE('20.11.2006:00:00:00','DD.MM.YYYY:HH24:MI:SS') - TO_DATE('28.10.2006:03:36:03','DD.MM.YYYY:HH24:MI:SS'),2) FROM dual Resultset in Oracle: 22,85 In Teradata: how to get the same result set? SELECT (TIMESTAMP '2006-11-20 00:00:00' - TIMESTAMP '2006-10-28 03:36:03') DAY(4) TO SECOND(4) AS MYINTERVAL Resultset in Teradata: 22 20:23:57.0000 Please help me to get same format as oracle. Thanks for your help. Regards, TDHElp – TDHelp Jan 22 at 7:21
See my answer above, I have extended it to meet your needs. – Rob Paller Jan 22 at 14:16
yes, they are little hard to handle, I was able to handle till seconds_elapsed yesterday night and I did not know about days between. That is working. Thanks a lot for your time and clarifying this. Regards, TDHelp – TDHelp Jan 22 at 16:45

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.