Selecting the minimum difference between two dates in Oracle when the dates are represented as UNIX timestamps

There are many question posted about getting the diffe开发者_开发百科rence between two dates in Oracle. My question is requires the query to do a couple more things.

Here's how far I have got at the moment

select m_bug_t.date_submitted, m_bug_history_t.date_modified
from m_bug_t, m_bug_history_t
where m_bug_t.id = m_bug_history_t.bug_id
and field_name = 'status'
and new_value = '100'

So far I get a set of date pairs returned like this

date_submitted | date_modified
1314894774     | 1315906468
...
...

I want to convert these numbers to dates, find the difference between them and then get the minimum of all the results. I want the difference to be represented as days.

Any ideas how you do this?

Thanks very much :).

Well, Unix timestamps are expressed as a number of seconds since 01 Jan 1970, so if you subtract one from the other you get the difference in seconds. The difference in days is then simply a matter of dividing by the number of seconds in a day:

(date_modified - date_submitted) / (24*60*60)

or

(date_modified - date_submitted) / 86400

To convert UNIX time to a date you can use:

DATE '1970-01-01' + numtodsinterval(:unix_time_stamp, 'second')

In SQL when you substract two dates you will get the difference in days so you could write:

SELECT MIN(dt_mod - dt_sub)
  FROM (SELECT DATE '1970-01-01' 
               + numtodsinterval(m_bug_t.date_submitted, 'second') dt_sub,
                DATE '1970-01-01' 
                + numtodsinterval(m_bug_history_t.date_modified, 'second') dt_mod
           FROM m_bug_t, m_bug_history_t
          WHERE m_bug_t.id = m_bug_history_t.bug_id
            AND field_name = 'status'
            AND new_value = '100')

Of course as others have suggested you don't really need to do this DATE conversion, you could just substract your 2 timestamps (difference in seconds) and convert the result in days.