Marek Olšavský: blog

V PostgreSQL přibylo (v řadě 9.x) několik zajímavých typů. Typ json/jsonb asi zaujal více a našel jsem hodně článků (není se čemu divit při popularitě NoSQL databází pro ukládání různě strukturovaných dokumentů); rozšíření o intervaly (range types) si možná všimlo méně vývojářů. Typ interval umí PgSQL od verze 9.2.

Předem se musím omluvit jazykovým puristům. Vím že množné číslo slova datum jsou data, ale ve světě práce s daty (ve smyslu hodnot/údajů nejen časových) mi připadá slovo datumů/datumy jednoznačné pro časový údaj.

Co je interval se učí na středních školách. Zjednodušeně jde o množinu hodnot, která je ohraničená. Podle toho zda tam hraniční body patří mluvíme o intervalech otevřených (hraniční body tam nepatří), uzavřených (hraniční body do množniny patří), polootevřených/polouzavřených (podle toho, zda tam patří horní/dolní mez).

Klasickou situaci pro intervaly zažil asi každý kdo pracuje s databázemi (hlavně pokud někdy potřeboval zapsat třeba otevírací dobu). Prostě se definují dva atributy hodnota_od a hodnota_do, následně se do podmínek dává něco ve smyslu WHERE "actvalue" BETWEEN "hodnota_od" AND "hodnota_do" (eventulně pomocí operátorů > < =). Pokud potřebujete zjistit průnik dvou takto definovaných intervalů, nebo zda jeden leží uvnitř druhého, podmínky se náležitě komplikují.

Běžné intervaly

Číselné intervaly se hodí například když máte přibližný rozpočet na výrobek (15–20 tisíc KČ) a cena se pohybuje v pásmu podle vybavenosti, takže hledáte kdy se intervaly protínají. Nebo agregujete útraty zákazníka a pak podle dřívější útraty dáváte slevu. Datumové/časové intervaly se budou hodit třeba pro rezervační systém.

Výchozí intervalové typy:

• int4range – interval mezi integery, nespojitý (jen celá čísla)
• int8range – interval mezi bigint, nespojitý
• numrange – spojitý interval reálných čísel
• tsrange – interval mezi datumy+časem bez časové zóny
• tstzrange – interval mezi datumy+časem s časovou zónou
• daterange – interval mezi datumy

Bohužel chybí timerange, ale jeden z možných workaroundů je popsán níže, v části „Čas“.

Pro otevřenou stranu intervalu se používá standardní kulatá závorka {(, )}, pro uzavřenou stranu hranatá {[, ]}. Nezadáním jedné hodnoty se určí nekonečno.

Příklady:

• '[1,9)'::int4range – množina celých čísel 1, 2, 3, 4, 5, 6, 7, 8
• '(1,2)'::numrange – všechna čísla mezi 1 a 2, ale kromě těchto dvou hraničních (1 < value < 2)
• '[2017-05-22, 2017-05-25)'::daterange – rezervace v hotelu, 22. května je první noc pro hosta, 25. května dopoledne odjede a odpoledne může ubytovat další host.

    CREATE TABLE pricelist(
      pricelist_id serial,
      price numrange,
      title varchar(200)
    );

    INSERT INTO
      pricelist(price, title)
    VALUES
       ('[1800, 2799]', 'BlackPhone'),
       ('(2300,2799]','BackDoors Phone'),
       ('(2800,4000)','Selfie Blade'),
       (numrange(3500,9000,'[]'),'ePhone');

    SELECT * FROM pricelist WHERE price @> 2800::numeric;
    -- Žádný výsledek? Pohled na otevřené a uzavřené strany intervalů napoví.
    SELECT * FROM pricelist WHERE price && numrange(2750,2850,'[]')
    --Vypíší se všechny položky, kde se moje cenové možnosti protínají s nabídkou
    SELECT * FROM pricelist WHERE price && '[3400,)'::numrange;
    --vše dražší, než 3500 :-)
    SELECT title, lower(price) FROM pricelist WHERE price && '[3500,)'::numrange;
    --Dolní mez cenového rozsahu při podmínce že cena (nějakého stupně výbavy) je > 3400

Abych nezapoměl, doporučovaným typem indexu jsou GiST a SP-GiST.

Nehodlám zatěžovat obsáhlým přepisem patřičné kapitoly oficiální dokumentace do češtiny, základní typy intervalů a práce s nimi jsou tam popsány velmi dobře.

Čas

Zajímavější je práce jen s časem, třeba u otevírací doby, nebo rozpisu směn. U obého může nastat i varianta času přes půlnoc, kdy je čas_od větší než čas_do (například herna má otevřeno v době 14:00–5:00) a v takovém případě je nutné použít „obezličku“ (neboli Cimrmanovský úkrok stranou).

Je potřebné si definovat vlasní typ pro atributy (z důvodu konzistence pojmenovaný timerange):

create type timerange as range (subtype = time);

    CREATE TABLE rests(
      rests_id serial,
      ohours timerange,
      restname varchar(200)
    );

    insert into rests(ohours, restname) values('[10:00:00,23:00:00]','Perla');
    -- Toto je OK
    insert into rests(ohours, restname) values('[17:00:00,03:00:00]','Perla herna');
    -- Tato varianta není v pořádku, intervaly vyžadují meze podle velikosti (od < do)

    --modifikujeme tabulku o atribut rev_ohours (reversed, takže v intervalu budou zavírací časy)
    ALTER TABLE rests ADD COLUMN rev_ohours BOOLEAN DEFAULT False;

    INSERT INTO rests(ohours,rev_ohours, restname) VALUES('(03:00:00,17:00:00)',TRUE,'Perla herna'); 

Po modifikaci tabulky je vložena „zavírací doba“ avšak je potřebné si povšimnout si i změny intervalu z uzavřeného na otevřený, protože u otevírací doby chceme i krajní časy. Dotaz se trochu zkomplikuje.

    SELECT * FROM "rests" WHERE
    CASE WHEN "rev_ohours"=TRUE THEN
      ("ohours" @> '23:30:00'::time) = FALSE
    ELSE
      ("ohours" @> '23:30:00'::time) = TRUE
    END;

Dotaz zjistí, který podnik má otevřeno ve 23:30, ale bez intervalu, tj. s atributy ohours_start a ohours_end bude dotaz trochu komplikovanější:

    SELECT * FROM "rests" WHERE
    CASE WHEN "ohours_start" < "ohours_end" THEN
        '23:30:00' BETWEEN "ohours_start" AND "ohours_end"
    ELSE
        ('23:30:00'::time <= "ohours_start") AND ('23:30:00'::time >= "ohours_end");

Bez testování si troufám odhadnout, že varianta s použitím intervalu bude rychlejší (pracuje se s méně atributy), rozhodně je dotaz přehlednější.

Závěrem

Intervaly nevyužije každý, ale důvod proč se jim vyhnout není. Škoda, že nejde o běžné rozšíření datových typů i v ostatních databázových systémech.

Určitě by nebylo na škodu zapracovat podporu `timerange`, ideálně s možností intervalu „přes půlnoc“. Vím minimálně o jednom projektu, kde by se časový interval hodil.

PHP PDO intervaly nepodporuje, pokud je potřeba nastavení přes webové rozhraní nezbývá než si attribut „podat“ rozebraný, viz dotaz na StackOverflow. Python, respektive psycopg2.extras podporu má a tím se vracím k tomu, že Flask (Python) je dobrá volba, pokud se nejedná o běžný obsahový web :-).