当日期表示为UNIX时间戳时，在Oracle中选择两个日期之间的最小差异 [英] Selecting the minimum difference between two dates in Oracle when the dates are represented as UNIX timestamps

问题描述

关于在Oracle中获取两个日期之间的差额，有很多问题.我的问题是要求查询执行更多操作.

There are many question posted about getting the difference between two dates in Oracle. My question is requires the query to do a couple more things.

这是我目前所走的距离

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.

您有什么想法吗?

非常感谢:).

推荐答案

好吧，Unix时间戳表示为自1970年1月1日以来的秒数，因此，如果您从另一个中减去一个，则会得到以秒为单位的差.那么，天数的差异就是除以一天中的秒数即可.

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)

或

(date_modified - date_submitted) / 86400

这篇关于当日期表示为UNIX时间戳时，在Oracle中选择两个日期之间的最小差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！