Link to home
Start Free TrialLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on

oracle differnce between two timestamps

Hi Team,

I have two timestamps  values , how to find the difference between them

01-MAR-17 10.22.05.033085000 AM -06:00
01-MAR-17 10.22.05.033795000 AM -06:00
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

If you're looking for something like NUMTODSINTERVAL but in reverse, to give you some time units (days/hours/seconds...) that doesn't exist, but it is fairly easy to create


   CREATE FUNCTION dsintervaltonum(p_interval IN INTERVAL DAY TO SECOND, p_unit IN VARCHAR2)
        RETURN NUMBER
        DETERMINISTIC
    IS
        illegal_argument   EXCEPTION;
        PRAGMA EXCEPTION_INIT(illegal_argument, -1760);
    BEGIN
        CASE UPPER(p_unit)
            WHEN 'SECOND'
            THEN
                RETURN   EXTRACT(DAY FROM p_interval) * 86400
                       + EXTRACT(HOUR FROM p_interval) * 3600
                       + EXTRACT(MINUTE FROM p_interval) * 60
                       + EXTRACT(SECOND FROM p_interval);
            WHEN 'MINUTE'
            THEN
                RETURN   EXTRACT(DAY FROM p_interval) * 1440
                       + EXTRACT(HOUR FROM p_interval) * 60
                       + EXTRACT(MINUTE FROM p_interval)
                       + EXTRACT(SECOND FROM p_interval) / 60;
            WHEN 'HOUR'
            THEN
                RETURN   EXTRACT(DAY FROM p_interval) * 24
                       + EXTRACT(HOUR FROM p_interval)
                       + EXTRACT(MINUTE FROM p_interval) / 60
                       + EXTRACT(SECOND FROM p_interval) / 3600;
            WHEN 'DAY'
            THEN
                RETURN   EXTRACT(DAY FROM p_interval)
                       + EXTRACT(HOUR FROM p_interval) / 24
                       + EXTRACT(MINUTE FROM p_interval) / 1440
                       + EXTRACT(SECOND FROM p_interval) / 86400;
            ELSE
                RAISE illegal_argument;
        END CASE;
    END dsintervaltonum;

Open in new window



example usage...

SELECT dsintervaltonum(ts2 - ts1, 'SECOND')
  FROM (SELECT TO_TIMESTAMP_TZ(
                   '01-MAR-17 10.22.05.033085000 AM -06:00',
                   'dd-MON-RR hh.mi.ss.ff AM TZH:TZM'
               )
                   ts1,
               TO_TIMESTAMP_TZ(
                   ' 01-MAR-17 10.22.05.033795000 AM -06:00 ',
                   'dd-MON-RR hh.mi.ss.ff AM TZH:TZM'
               )
                   ts2
          FROM DUAL);


DSINTERVALTONUM(TS2-TS1,'SECOND')
---------------------------------
                           .00071

Open in new window

Avatar of Swaminathan K

ASKER

awesome
SELECT TO_TIMESTAMP_TZ(
                   '01-MAR-17 10.22.05.033085000 AM -06:00',
                   'dd-MON-RR hh.mi.ss.ff AM TZH:TZM'
               ) -   TO_TIMESTAMP_TZ(
                   ' 01-MAR-17 10.22.05.033795000 AM -06:00 ',
                   'dd-MON-RR hh.mi.ss.ff AM TZH:TZM'
               ) from dual;  this worked for me as mentioned by slightwv . I have just sdstuber values.
I don't understand what you're trying to say in that last post.