«
»

How to find readable time difference bettween Oracle date types?

By inanme

It is not crucial, but it is cool. This way you can find time date/time difference nicely.

SELECT

lpad(EXTRACT(HOUR FROM(dend_date – dstart_date) DAY TO SECOND),2,’0′) || ‘ hour ‘ ||
lpad(EXTRACT(MINUTE FROM(dend_date – dstart_date) DAY TO SECOND),2,’0′) || ‘ minute ‘ ||
lpad(EXTRACT(SECOND FROM(dend_date – dstart_date) DAY TO SECOND),2,’0′) || ‘ second ‘ “Interval”
FROM process_log
order by dstart_date desc;

ref::Oracle® Database SQL Reference
10g Release 2 (10.2)
B14200-02

Possibly related posts: (automatically generated)

This entry was posted on January 29, 2007 at 7:43 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “How to find readable time difference bettween Oracle date types?”

  1. H.Tonguç Yılmaz Says:
    January 29, 2007 at 9:08 am

    Very usefull but unknown function, always suprises me during internal educations how people reacts to it :)

    Also it applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment;

    EXTRACT(XMLType_Instance>, , )

    EXTRACT(XML) is similar to the EXISTSNODE function.

    SELECT EXTRACT(YEAR FROM SYSDATE) year, EXTRACT(MONTH FROM SYSDATE) month FROM DUAL;

    YEAR MONTH
    ———- ———-
    2007 1

Leave a Reply