Oracle中两个年月日值之间的差异 [英] Difference between two Year Month and Day values in Oracle
问题描述
我有两组Year，Month和Day值.例如. 15年7个月23天和7年9个月12天.是否可以按年，月和天来找到两个这样的值之间的差异?我曾考虑过将值转换为天数，计算差值，然后再次将结果转换回年，月和天?为此，我必须假设每月30天和每年365天.这种方法行吗?
I have two sets of Year, Month and Day values. Eg. 15 years 7 months and 23 Days and 7 years 9 months and 12 days. Can the difference between two such values be found in terms of Years, Month and Days? I have thought of converting the values to days, compute the difference and again convert the result back to Years, Months and Days? For this, I have to assume 30 days/month and 365 days/year. Will this approach be ok?
推荐答案
仔细考虑后，您会意识到，几个月就无法计算两个时间间隔"之间的差值.仅仅因为减去几个月会导致不同的天数.您可以减去年，可以减去周，可以减去天，...可以减去天到秒，可以减去年到月.但是，您不能减去年份.
When you think about it thoroughly, you'll realize that you can't calculate a difference of two "time intervals" when months are in place; simply because a subtraction of months can result in different number of days. You can subtract years, you can subtract weeks, you can subtract days,... you can subtract days-to-seconds, you can subtract years-to-months. However, you can't subtract years-to-days.
示例:
SQL> select timestamp'1915-07-23 00:00:00' - timestamp'1907-09-12 00:00:00' as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002871 00:00:00
以1900年1月1日为准，这是您的15年7个月23天减去7年9个月12天.这使我们相距2871天.
This is your 15 years, 7 months, 23 days minus 7 years, 9 months, 12 days when based on January 1, 1900. This gave us 2871 days difference.
但是，请考虑以下两个示例，它们只是过去了1个月和6个月而已
However, consider the following two examples, simply shifted by 1 and 6 months to past
select timestamp'1915-06-23 00:00:00' - timestamp'1907-08-12 00:00:00' as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002872 00:00:00
select timestamp'1915-01-23 00:00:00' - timestamp'1907-03-12 00:00:00' as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002874 00:00:00
SQL>
现在这些给了我们2872天和2874天的天差.
These now gave us 2872 and 2874 days of difference.
现在，谈到可能的减法...
(a)减去年月间隔
SQL> select interval'1915-07' year(4) to month - interval'1907-09' year(4) to month as diff_year_to_month_interval from dual;
DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10
select interval'1915-06' year(4) to month - interval'1907-08' year(4) to month as diff_year_to_month_interval from dual;
DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10
select interval'1915-01' year(4) to month - interval'1907-03' year(4) to month as diff_year_to_month_interval from dual;
DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10
SQL>
这三个正确地产生了7年零10个月的差异.
All three correctly produce a difference of 7 years and 10 months.
(b)减去秒间隔
SQL> select interval'15 01:02:03' day(2) to second - interval'07 02:03:04' day(2) to second as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59
select interval'14 00:01:02' day(2) to second - interval'06 01:02:03' day(2) to second as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59
select interval'09 11:12:13' day(2) to second - interval'01 12:13:14' day(2) to second as diff_day_to_second_interval from dual;
DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59
SQL>
这三个结果均相同，因为所有三个都是从秒到秒的间隔的减去，并且对间隔值的天/小时/分钟/秒部分进行了一致的偏移.
All three produce the same results, as all three are subtractions of day-to-second intervals with consistent offsetting of the day/hour/minute/second parts of the interval values.
(c)减去年度间隔
正如我所说:不可能.在Oracle中甚至没有年复一年的间隔.数据库服务器的制造商知道为什么他们决定不将它们添加到引擎中.
As I said: Not possible. There even is no such thing as year-to-day interval in Oracle; makers of the DB server knew why they decided not to add those to the engine.
这篇关于Oracle中两个年月日值之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
