Konwertuj zakres dat na opis interwału

W ostatnim projekcie wymagano podania, kiedy zasób zostanie w pełni wykorzystany. Oprócz daty wyczerpania kalendarza poproszono mnie o pokazanie pozostałego czasu w formacie angielskim, np. „1 rok, 3 miesiące do końca”.

Wbudowana DATEDIFFfunkcja

Zwraca liczbę ... określonych granic datapart przekroczonych między określoną datą początkową i końcową.

Jeśli zastosuje się go w takiej postaci, może to powodować mylące lub mylące wyniki. Na przykład użycie przedziału YEAR pokazałoby, że 1999-12-31 (RRRR-MM-DD) i 2000-01-01 są w odstępie jednego roku, podczas gdy zdrowy rozsądek mówi, że te daty są oddzielone tylko o 1 dzień. I odwrotnie, stosując przedział DAY 1999-12-31 i 2010-12-31 dzieli się 4018 dni, podczas gdy większość ludzi uważa, że ​​„11 lat” to lepszy opis.

Począwszy od liczby dni i obliczania miesięcy i lat, podatne byłyby na błędy w roku przestępnym i wielkości miesiąca.

Zastanawiałem się, jak można to zaimplementować w różnych dialektach SQL? Przykładowe dane wyjściowe obejmują:

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

Zdarza mi się używać programu SQL Server 2008R2, ale jestem zainteresowany, aby dowiedzieć się, jak poradzą sobie z tym inne dialekty.

Poniższe rozwiązanie dotyczy programu SQL Server. Podejście jest podobne do Serga, ponieważ zapytanie używa tylko funkcji DATEADD i DATEDIFF. To nie oznacza jednak, stanowią odstępach negatywnych ( fromdate > todate ), i wywodzi latach i miesiącach od całkowitej różnicy miesięcy:

WITH
  MonthDiff AS
  (
    SELECT
      t.FromDate,
      t.ToDate,
      t.ExpectedResult,
      Months = x.Months - CASE WHEN DAY(t.FromDate) > DAY(t.ToDate) THEN 1 ELSE 0 END
    FROM
      dbo.TestData AS t
      CROSS APPLY (SELECT DATEDIFF(MONTH, t.FromDate, t.ToDate)) AS x (Months)
  )
SELECT
  t.FromDate,
  t.ToDate,
  t.ExpectedResult,
  Result = ISNULL(NULLIF(ISNULL(x.Years  + CASE x.Years  WHEN '1' THEN ' year '  ELSE ' years '  END, '')
                       + ISNULL(x.Months + CASE x.Months WHEN '1' THEN ' month ' ELSE ' months ' END, '')
                       + ISNULL(x.Days   + CASE x.Days   WHEN '1' THEN ' day '   ELSE ' days '   END, ''), ''), '0 days')
FROM
  MonthDiff AS t
  CROSS APPLY
  (
    SELECT
      CAST(NULLIF(t.Months / 12, 0) AS varchar(10)),
      CAST(NULLIF(t.Months % 12, 0) AS varchar(10)),
      CAST(NULLIF(DATEDIFF(DAY, DATEADD(MONTH, t.Months, t.FromDate), t.ToDate), 0) AS varchar(10))
  ) AS x (Years, Months, Days)
;

Wynik:

FromDate    ToDate      ExpectedResult                 Result
----------  ----------  -----------------------------  -----------------------------
1999-12-31  1999-12-31  0 days                         0 days
1999-12-31  2000-01-01  1 day                          1 day 
2000-01-01  2000-02-01  1 month                        1 month 
2000-02-01  2000-03-01  1 month                        1 month 
2000-01-28  2000-02-29  1 month, 1 day                 1 month 1 day 
2000-01-01  2000-12-31  11 months, 30 days             11 months 30 days 
2000-02-28  2000-03-01  2 days                         2 days 
2001-02-28  2001-03-01  1 day                          1 day 
2000-01-01  2001-01-01  1 year                         1 year 
2000-01-01  2011-01-01  11 years                       11 years 
9999-12-30  9999-12-31  1 day                          1 day 
1900-01-01  9999-12-31  8099 years 11 months 30 days   8099 years 11 months 30 days 

Ta odpowiedź pokazuje implementację przy użyciu funkcji CLR programu SQL Server (2005+).

-- Enable CLR (if necessary)
EXECUTE sys.sp_configure 
    @configname = 'clr enabled',
    @configvalue = 1;

RECONFIGURE;

Montaż i funkcja

CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11134570000000000000000E00002210B010B00000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B42900005700000000400000A802000000000000000000000000000000000000006000000C0000007C2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000F0290000000000004800000002000500EC210000900600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003008601000001000011020A0F01280600000A0F00280600000A590B160C160D072C5C0F00280700000A0F01280700000A30200F00280700000A0F01280700000A33140F00280800000A0F01280800000A31040717590B120007280900000A0A2B1D120017280A00000A03280B00000A2C5B0817580C120017280A00000A0A0603280C00000A2C451200280600000A7E0D00000A13051205280600000A33C31200280700000A7E0D00000A13061206280700000A33AC2B150917580D120023000000000000F03F280E00000A0A0603280C00000A2DE21F64730F00000A13040716313D1104076F1000000A26110407172E0772010000702B05720F0000706F1100000A2611040816300B091630077E1200000A2B05721B0000706F1100000A26081631391104086F1000000A26110408172E0772210000702B0572310000706F1100000A261104091630077E1200000A2B05721B0000706F1100000A2609163006072D24082D211104096F1000000A26110409172E07723F0000702B05724B0000706F1100000A2611046F1300000A2A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E000014020000F001000023537472696E6773000000000404000058000000235553005C0400001000000023475549440000006C0400002402000023426C6F620000000000000002000001471502000900000000FA253300160000010000000A000000020000000200000003000000140000000500000001000000010000000200000000000A0001000000000006003D0036000600440036000A008E0073000600BB00A8001300CF0000000600FE00DE0006001E01DE000A00460173000600C501B9010600DA0136000000000001000000000001000100010010001800000005000100010050200000000096004D000A000100E22100000000861861001200040000000000000000000100A00000000200A500190061001200210061004800310061004E0039006100120041006100120011005B01B60111006401B60111006E01B60111007601BA0111007F01BA0111008901C00111009C01C0011100A801C8011100B101CC01490061004E004900D301D2014900D301D8015100E101DE010900E701E10109006100120020002B00530024000B0016002E001300F3012E001B00FC012E0023000502E5010480000000000000000000000000000000003C01000002000000000000000000000001002D000000000002000000000000000000000001006700000000000000003C4D6F64756C653E004461746162617365312E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A656374004461746554696D6500496E74657276616C4465736372697074696F6E002E63746F720053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650046726F6D00546F0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053716C46756E6374696F6E417474726962757465006765745F59656172006765745F4D6F6E7468006765745F446179004164645965617273004164644D6F6E746873006F705F4C6573735468616E4F72457175616C006F705F4C6573735468616E004D617856616C756500416464446179730053797374656D2E5465787400537472696E674275696C64657200417070656E6400537472696E6700456D70747900546F537472696E6700000D200079006500610072007300000B2000790065006100720000052C002000000F20006D006F006E00740068007300000D20006D006F006E0074006800000B200064006100790073000009200064006100790000000000AFDAAB526E833740886DDFF9139712E60008B77A5C561934E0890700020E1109110903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A656400000005200101111504200101088161010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D6513496E74657276616C4465736372697074696F6E0320000805200111090807000202110911090306110905200111090D05200112250805200112250E02060E0320000E0D070711090808081225110911090801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000B111345700000000020000001C01000098280000980A000052534453F841C8A989DDDC4098D9FD78225EB30502000000633A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C56697375616C2053747564696F20323031355C50726F6A656374735C4461746162617365315C4461746162617365315C6F626A5C52656C656173655C4461746162617365312E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DC2900000000000000000000FE290000002000000000000000000000000000000000000000000000F02900000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.IntervalDescription
(
    @From date, 
    @To date
)
RETURNS nvarchar(100)
AS EXTERNAL NAME 
    DBA.UserDefinedFunctions.IntervalDescription;

Stosowanie

SELECT 
    TD.FromDate,
    TD.ToDate,
    TD.ExpectedResult, 
    IntervalDescription = dbo.IntervalDescription(TD.FromDate, TD.ToDate) 
FROM dbo.TestData AS TD;

Wynik

Źródło

_{Nie jestem programistą C #!}

using Microsoft.SqlServer.Server;
using System;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess = DataAccessKind.None,
        SystemDataAccess = SystemDataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        Name = "IntervalDescription"
        )
    ]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 100)]
    public static string IntervalDescription(DateTime From, DateTime To)
    {
        var workDate = From;
        int years = To.Year - From.Year;
        int months = 0;
        int days = 0;

        if (years != 0)
        {
            if (From.Month > To.Month || (From.Month == To.Month && From.Day > To.Day))
            {
                years--;
            }
            workDate = workDate.AddYears(years);
        }

        while (workDate < To && (workDate.Year != DateTime.MaxValue.Year || workDate.Month != DateTime.MaxValue.Month))
        {
            if (workDate.AddMonths(1) <= To)
            {
                months++;
                workDate = workDate.AddMonths(1);
            }
            else
            {
                break;
            }
        }

        while (workDate < To)
        {
            days++;
            workDate = workDate.AddDays(1);
        }

        StringBuilder sb = new StringBuilder(100);

        if (years > 0)
        {
            sb.Append(years);
            sb.Append(years == 1 ? " year" : " years");
            sb.Append((months > 0 || days > 0) ? ", " : string.Empty);
        }

        if (months > 0)
        {
            sb.Append(months);
            sb.Append(months == 1 ? " month" : " months");
            sb.Append(days > 0 ? ", " : string.Empty);
        }

        if (days > 0 || (years == 0 && months == 0))
        {
            sb.Append(days);
            sb.Append(days == 1 ? " day" : " days");
        }

        return
            sb.ToString();

    }
}

Moja wersja, zaimplementowana w SQL Server 2008R2 SP2.

CREATE FUNCTION dbo.ReadableInterval(
    @FromDate AS date,
    @ToDate AS date
)
RETURNS TABLE AS RETURN 
(
with YearStep as
(
    select
        max(n1.Number) as YearNumber
    from dbo.Numbers as n1
    where n1.Number <= DATEDIFF(YEAR, @FromDate, @ToDate)  -- see comment (A)
    and DATEADD(YEAR, n1.Number, @FromDate) <= @ToDate     -- see comment (B)
)
, MonthStep as
(
    select
        max(n2.Number) as MonthNumber
    from dbo.Numbers as n2
    cross apply YearStep as y1
    where n2.Number <= DATEDIFF(MONTH, DATEADD(YEAR, y1.YearNumber, @FromDate), @ToDate)
    and DATEADD(MONTH, n2.Number, DATEADD(YEAR, y1.YearNumber, @FromDate)) <= @ToDate
)
, DayStep as
(
    select
        DATEDIFF(day, DATEADD(MONTH, m1.MonthNumber, DATEADD(YEAR, y2.YearNumber, @FromDate)), @ToDate) as DayNumber
    from MonthStep as m1
    cross apply YearStep as y2
)
select
    y.YearNumber,
    m.MonthNumber,
    d.DayNumber
from YearStep as y
cross apply MonthStep as m
cross apply DayStep as d
)

Przy podanych danych testowych wyniki są

select
    td.FromDate,
    td.ToDate,
    td.ExpectedResult,
    ri.YearNumber as Years,
    ri.MonthNumber as Months,
    ri.DayNumber as [Days]
from dbo.TestData as td
cross apply dbo.ReadableInterval(td.FromDate, td.ToDate) as ri;

FromDate   ToDate     ExpectedResult               Years Months Days
---------- ---------- ---------------------------- ----- ------ ----
1999-12-31 1999-12-31 0 days                           0      0    0
1999-12-31 2000-01-01 1 day                            0      0    1
2000-01-01 2000-02-01 1 month                          0      1    0
2000-02-01 2000-03-01 1 month                          0      1    0
2000-01-28 2000-02-29 1 month, 1 day                   0      1    1
2000-01-01 2000-12-31 11 months, 30 days               0     11   30
2000-02-28 2000-03-01 2 days                           0      0    2
2001-02-28 2001-03-01 1 day                            0      0    1
2000-01-01 2001-01-01 1 year                           1      0    0
2000-01-01 2011-01-01 11 years                        11      0    0
9999-12-30 9999-12-31 1 day                            0      0    1
1900-01-01 9999-12-31 8099 years 11 months 30 days  8099     11   30

Wyjaśnienie

Moje ogólne podejście polega na tym, aby pójść naprzód od wcześniejszej daty, najpierw w latach, potem w miesiącach, a następnie w dniach. Na każdym poziomie szczegółowości celem jest zbliżenie się do daty końcowej bez przekraczania jej, a następnie przejście do następnego niższego poziomu.

Korzystam z tabeli liczb, aby ułatwić obliczenia zbliżające się, ale nie do końca. Z tej tabeli DATEADDmogę znaleźć największą liczbę lat / miesięcy / dni, które poprzedzają ToDate- komentarz (B) w kodzie.

Ponieważ szukałem numeru MAX, a moja tabela liczb jest na nim skupiona, optymalizator wykonał skan malejący, podając wartości do DATEADD. Powodowało to błędy przepełnienia daty, ponieważ Numbers zawiera ponad 100 000 wierszy. DATEADD(YEAR, 100000, @FromDate)jest większy niż 9999-12-31 i pojawia się błąd. Predykat (A) określa górny limit wartości liczby, od której rozpoczyna się skanowanie do tyłu, unikając przepełnienia daty. W związku z tym plan zapytań przechodzi przez bardzo niewiele wierszy dla nawet bardzo dużych zakresów dat.

Podejście to stosuje się do wyszukiwania lat i miesięcy, z wyjątkiem tego, że punkt początkowy dla miesięcy jest przyspieszony o tyle lat, ile znalazłem w pierwszym CTE. DAYS to mój najniższy poziom szczegółowości, więc wystarczy DATEDIFF.

Można to rozszerzyć na większą szczegółowość, zwracając interwał w godzinach, minutach i sekundach, jeśli to konieczne.

PostgreSQL obsługuje agefunkcję gotową do użycia:

select
  FromDate,
  ToDate,
  ExpectedResult,
  age(ToDate, FromDate)
from TestData;

Daje to pożądany rezultat, daje dodatkowe wartości czasu.

FromDate      ToDate        ExpectedResult                  age
----------    ----------    ----------------------------    --------------------------
1999-12-31    1999-12-31    0 days                          00:00:00
1999-12-31    2000-01-01    1 day                           1 day
2000-01-01    2000-02-01    1 month                         1 mon
2000-02-01    2000-03-01    1 month                         1 mon
2000-01-28    2000-02-29    1 month, 1 day                  1 mon 1 day
2000-01-01    2000-12-31    11 months, 30 days              11 mons 30 days
2000-02-28    2000-03-01    2 days                          2 days
2001-02-28    2001-03-01    1 day                           1 day
2000-01-01    2001-01-01    1 year                          1 year
2000-01-01    2011-01-01    11 years                        11 years
9999-12-30    9999-12-31    1 day                           1 day
1900-01-01    9999-12-31    8099 years 11 months 30 days    8099 years 11 mons 30 days

Wersja bez numbertabeli lub licznika wymagana. Daje taki sam wynik na danych testowych Michaela Greena. Różnią się danymi, gdzie @FromDate > @ToDate. ReadableInterval2zwraca wartości ujemne przeciwne zerom.

CREATE FUNCTION dbo.ReadableInterval2(
    @FromDate AS date,
    @ToDate AS date
)
RETURNS TABLE AS RETURN 
(with checkData as (
    select 
       fromDate = case when @FromDate > @ToDate then @ToDate else @FromDate end,
       toDate = case when @FromDate <= @ToDate then @ToDate else @FromDate end,
       k = case when @FromDate > @ToDate then -1 else 1 end
), MonthStep as (
    select k, FromDate, ToDate,
        YearNumber = x.months / 12,
        MonthNumber = x.months % 12
    from checkdata
    cross apply(
        select months = DATEDIFF(MONTH, FromDate, ToDate)
            - case when DAY(FromDate) > DAY(ToDate) then 1 else 0 end
        ) x
)
select YearNumber = k*YearNumber, 
      MonthNumber = k*MonthNumber,
      DayNumber = k*DATEDIFF(day, DATEADD(MONTH, MonthNumber, DATEADD(YEAR, YearNumber, FromDate)), ToDate) 
    from MonthStep 
)