如何在firebird函数datediff中获得正确的年份、月份和日期

Question

我不得不问另一个关于Firebird中的datediff的问题。在这种情况下，我不知道如何得到正确的结果：工人x有两份雇佣合同，第一份是在1988-09-15年至2000-03-16年期间，第二份是在2000-03-16至2005-02-28年期间。我想得到的结果是16年5个月零3天，因为第一个结果是11年6个月零1天，第二个结果是4年11个月零2天。有人能告诉我如何在火鸟中做到这一点吗。我最想知道的是，从几个月(17个月(的总和中，我如何做到5个月，其他12个月增加一年的价值。现在我有这样的SQL：

SELECT
a.id_contact, 
sum(floor(datediff(day, a.DATE_FROM, a.DATE_TO)/365.25)) as YEAR,
mod(sum(mod(floor(datediff(day, a.DATE_FROM, a.DATE_TO)/30.41),12)),12) as MTH
FROM KP a
group by a.id_contact

然后我有5个月，但我没有12个月的时间来增加一年的价值。请帮帮我…

Answer 1

您应该先对天数求和，然后对结果求和，再计算Y、M、D

SELECT
KP3.id_contact 
, (KP3.D2-KP3.D1) / (12*31) AS Y
, ((KP3.D2-KP3.D1) - ((KP3.D2-KP3.D1) / (12*31)) * 12 * 31) / 31 AS M
, CAST(MOD((KP3.D2-KP3.D1) - (((KP3.D2-KP3.D1) / (12*31)) * 12 * 31), 31) AS INTEGER) AS D
FROM
(SELECT
KP2.id_contact, SUM(KP2.D1) AS D1, SUM(KP2.D2) AS D2
FROM
(
SELECT
KP.id_contact, DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO) / 12 AS Y, CAST(MOD(DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO), 12) AS INTEGER) AS M 
, EXTRACT(YEAR FROM KP.DATE_FROM)*12*31+EXTRACT(MONTH FROM KP.DATE_FROM)*31+EXTRACT(DAY FROM KP.DATE_FROM) D1
, EXTRACT(YEAR FROM KP.DATE_TO)*12*31+EXTRACT(MONTH FROM KP.DATE_TO)*31+EXTRACT(DAY FROM KP.DATE_TO) D2 
FROM
KP  
) AS KP2
GROUP BY KP2.id_contact
) AS KP3

Answer 2

正确的方法似乎是测量在两项任务上花费的天数，然后将这些日期相加，然后将其转换为固有的不精确给予或接受类似于streeet谈话的年-月-天形式下面有更多关于这个的信息

借用Livius的转换查询，并将系数调整为更现实的，这将发展为：

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=2fba0ace6a70ae16a167ec838642dc28

在这里，我们一步一步地前进，从简单的区块发展到越来越复杂的区块，这最终给了我们16年零5个月零2天的时间：

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;

|VERSION||：------||3.0.5 |

<blockquote\

create table KP (
ID_CONTACT integer not null,
DATE_FROM date not null,
DATE_TO date not null
)
-- https://stackoverflow.com/questions/51551257/how-to-get-correct-year-month-and-day-in-firebird-function-datediff

>

✓

create index KP_workers on KP(id_contact)

✓

insert into KP values (1, '1988-09-15', '2000-03-16')

1行受影响

insert into KP values (1, '2000-03-16', '2005-02-28')

1行受影响

-- the sample data from https://stackoverflow.com/questions/60030543
-- might expose the rounding bug in my original formulae:
-- unexpected ROUNDING UP leading to NEGATIVE value for months
insert into KP values (2, '2018-02-08', '2019-12-01')

1行受影响

insert into KP values (2, '2017-02-20', '2018-01-01')

1行受影响

select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KP a

ID_CONTACT|DATE_FROM|DATE_TO|DAYS_COUNT---------：|：--------|：---------|：---------1 | 1988-09-15 | 2000-03-16 | 42001 | 2000-03-16 | 2005-02-28 | 18102|2018-02-08|2019-12-01|6612017年2月20日| 2018年1月1日| 315

<blockquote\

-- Original answer by Livius
SELECT
KP3.id_contact 
, KP3.D2-KP3.D1 as days_count
, (KP3.D2-KP3.D1) / (12*31) AS Y
, ((KP3.D2-KP3.D1) - ((KP3.D2-KP3.D1) / (12*31)) * 12 * 31) / 31 AS M
, CAST(MOD((KP3.D2-KP3.D1) - (((KP3.D2-KP3.D1) / (12*31)) * 12 * 31), 31) AS INTEGER) AS D
FROM
(SELECT
KP2.id_contact, SUM(KP2.D1) AS D1, SUM(KP2.D2) AS D2
FROM
(
SELECT
KP.id_contact, DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO) / 12 AS Y, CAST(MOD(DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO), 12) AS INTEGER) AS M 
, EXTRACT(YEAR FROM KP.DATE_FROM)*12*31+EXTRACT(MONTH FROM KP.DATE_FROM)*31+EXTRACT(DAY FROM KP.DATE_FROM) D1
, EXTRACT(YEAR FROM KP.DATE_TO)*12*31+EXTRACT(MONTH FROM KP.DATE_TO)*31+EXTRACT(DAY FROM KP.DATE_TO) D2 
FROM
KP  
) AS KP2
GROUP BY KP2.id_contact
) AS KP3

>

ID_CONTACT|DAYS_COUNT|Y|M|D---------：|：---------|：-|：-|-：1|6120|16|5|132|997|2|8|5

<blockquote\

select ID_CONTACT, sum(DAYS_COUNT) as DAYS_COUNT
from (
select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KP a
)
GROUP BY 1

>

ID_CONTACT|DAYS_COUNT---------：|：---------1 | 60102|976

<blockquote\

-- this step taken from https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=52c1e130f589ca507c9ff185b5b2346d
-- based on original Livius forumla with non-exact integer coefficients
-- it seems not be generating negative counts, but still shows very different results
SELECT
KP_DAYS.id_contact,
KP_DAYS.DAYS_COUNT / (12*31) AS Y,
((KP_DAYS.DAYS_COUNT) - ((KP_DAYS.DAYS_COUNT) / (12*31)) * 12 * 31) / 31 AS M,
CAST(MOD((KP_DAYS.DAYS_COUNT) - (((KP_DAYS.DAYS_COUNT) / (12*31)) * 12 * 31), 31) AS INTEGER) AS D
FROM
(
select ID_CONTACT, sum(DAYS_COUNT) as DAYS_COUNT
from (
select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KP a
)
GROUP BY 1  
) as KP_DAYS

>

ID_CONTACT|Y|M|D---------：|：-|：-|-：1|16|1|272|2|7|15

SELECT
KP_DAYS.id_contact, KP_DAYS.days_count
, FLOOR(KP_DAYS.DAYS_COUNT / 365.25) AS Y
, FLOOR( (KP_DAYS.DAYS_COUNT - (FLOOR(KP_DAYS.DAYS_COUNT / 365.25) * 365.25) ) / 30.5) AS M 
, CAST(MOD((KP_DAYS.DAYS_COUNT) - (((KP_DAYS.DAYS_COUNT) / 365.25) * 365.25), 30.5) AS INTEGER) AS D
FROM
(
select ID_CONTACT, sum(DAYS_COUNT) as DAYS_COUNT
from (
select a.*, datediff(day, a.DATE_FROM, a.DATE_TO) as DAYS_COUNT from KP a
)
GROUP BY 1  
) as KP_DAYS

ID_CONTACT|DAYS_COUNT|Y|M|D---------：|：---------|：-|：-|-：1|6010|16|5|22|976|2|8|1

请注意，以上内容在数学上仍然不正确。但应该给人一种时间戳的"直觉"。获得Y-M-D形式的时间跨度的精确度量的问题是没有实际意义的。

例如，您引用了3天，而此查询给出了2天。我看不出有什么错误。由于月份和年份不同，您无法正确测量月份的时间距离。这就像在城市中测量地理距离一样。

伦敦和巴黎之间有多少纽约？埃尔布鲁斯山有多少瓦锯高？你不可能有任何数学上正确的答案。

因此，你只能用非精确估计来回答。适合于街头演讲。因此，任何基于DateDiff的查询基本上都会生成一个完全有效的答案，即"2Y 10M给予或花费几天时间"——这个答案在"只给我整体印象"的情况下是有效的。

将这种简单的感觉与数学准确性的完美主义结合起来是不可能的。例如，假设你得到大约6Y的跨度。现在你应该考虑多少闰年？在1999年至2004年的"6Y"中有两个闰年，但在1998年至2003年的同一年中只有一个闰年。以下哪一项是"6Y"的正确度量？？？

然后我们有千禧年，2000年是闰年，但1900年不是。同样的"滑动窗口"问题会在"110Y"这样的时间跨度中给出不稳定的未定义闰年数。如果你想按照外行的看法，用"年和月"来计算时间跨度，你必须同意这会让事情变得简单、简单和不精确。几年中一天或几天的不匹配是常态，可以吗