SQL: Filtrujte datum a čas jako profík

Potřebujete snadno filtrovat záznamy typu DATE a TIMESTAMP?

Datumové a časové funkce má každá databáze, ale práce s nimi je někdy nepohodlná.

Naštěstí existuje efektivní a zároveň jednoduchý způsob, jak si takové nepohodlí usnadnit. Zejména v případech, kdy s datumovými sloupci pracujete často.

Rozdělení na části

Způsob, jak si filtrování zjednodušit, je rozdělení data a času na jednotlivé části. Do tabulky se k existujícímu sloupci, např. ODESLANO, přidají další sloupce, jako např. ODESLANO_ROK, ODESLANO_MESIC, ODESLANO_DEN, atd.

-- Vytvoř 5 náhodných záznamů pomocí CTE
WITH q AS (
  SELECT
    NOW() + (random() * INTERVAL '3 months') AS "ODESLANO"
  FROM generate_series(1,5)
)
SELECT
  "ODESLANO",
  EXTRACT(YEAR FROM "ODESLANO") AS "ODESLANO_ROK",
  EXTRACT(MONTH FROM "ODESLANO") AS "ODESLANO_MESIC",
  EXTRACT(DAY FROM "ODESLANO") AS "ODESLANO_DEN"
FROM q;

           ODESLANO  | ODESLANO_ROK | ODESLANO_MESIC | ODESLANO_DEN 
---------------------+--------------+----------------+--------------
 2024-12-01 23:46:23 |         2024 |             12 |            1
 2024-11-02 00:15:44 |         2024 |             11 |            2
 2024-11-16 00:01:32 |         2024 |             11 |           16
 2024-11-09 02:56:47 |         2024 |             11 |            9
 2024-11-21 16:31:22 |         2024 |             11 |           21

Vytvoření extra sloupců zlepší přehlednost a zjednoduší filtrování dat.

To ocení nejen začátečníci, ale i zkušenější uživatelé. A pokud data vyexportujete do Excelu, bude filtrování po jednotlivých sloupcích příjemnější.

Srovnání

Posuďte sami, co je jednodušší?

Použití datumových funkcí

-- Vyber všechny záznamy, které jsou ze 4. kvartálu a 16. hodiny
SELECT * 
FROM tabulka1
WHERE EXTRACT(QUARTER FROM "datum a čas") = 4
  AND EXTRACT(HOUR FROM "datum a čas") = 16
;

Filtrování podle sloupce

-- Vyber všechny záznamy, které jsou ze 4. kvartálu a 16. hodiny
SELECT * 
FROM tabulka1
WHERE "hodina" = 16
  AND "čtvrtletí" = 4
;

Pro většinu uživatelů je příjemnější a přehlednější ta druhá možnost.

Datumové funkce

SQL obsahuje spoustu šikovných datumových funkcí. Jednou z nich je EXTRACT, která vrací jednotlivé části z data a času.

Následující příklady ukazují použití v databázi PostgreSQL, ale datumovou funkci EXTRACT umí i ostatní databáze (MySQL, SQL Server, Oracle, Teradata, a další).

Některé pro ni používají ekvivalent DATEPART, popř. DATE_PART. Princip těchto funkcí je stejný na všech RDBMS.

SQL pro každý den

Rozšířené vydání

Jak si užít SQL a nezabloudit v kódu. Nebaví vás znovu vynalézat kolo? Inspirujte se SQL příklady z praxe nebo je klidně okopírujte! Šetřete svůj čas při každodenních analytických úkolech.

Prozkoumat

Vyzkoušejte online

Pokud nemáte přístup k PostgreSQL databázi, můžete si příklady vyzkoušet online.

Např. na OneCompiler nebo na SQL Fiddle.

Rozdělení datumových sloupců

-- Vytvoř tabulku s náhodnými daty
CREATE TABLE tabulka1
AS
WITH q AS (
  SELECT
    ROW_NUMBER() OVER () AS "řádek",
    NOW() + (random() * INTERVAL '3 months') AS "datum a čas"
  FROM generate_series(1,100)
)
SELECT
  "řádek",
  "datum a čas",
  EXTRACT(YEAR FROM "datum a čas") AS "rok",           -- 👈 Rok
  EXTRACT(QUARTER FROM "datum a čas") AS "čtvrtletí",  -- 👈 Čtvrtletí (kvartál)
  EXTRACT(WEEK FROM "datum a čas") AS "týden",         -- 👈 Týden
  EXTRACT(MONTH FROM "datum a čas") AS "měsíc",        -- 👈 Měsíc
  CASE EXTRACT(ISODOW FROM "datum a čas")
    WHEN 1 THEN 'po'
    WHEN 2 THEN 'út'
    WHEN 3 THEN 'st'
    WHEN 4 THEN 'čt'
    WHEN 5 THEN 'pá'
    WHEN 6 THEN 'so'
    WHEN 7 THEN 'ne'
  END AS "den v týdnu",                                -- 👈 Den v týdnu
  EXTRACT(DAY FROM "datum a čas") AS "den",            -- 👈 Den
  EXTRACT(HOUR FROM "datum a čas") AS "hodina",        -- 👈 Hodina
  EXTRACT(MINUTE FROM "datum a čas") AS "minuta",      -- 👈 Minuta
  EXTRACT(SECOND FROM "datum a čas")::INT AS "vteřina" -- 👈 Vteřina (jako celé číslo)
FROM q;

-- Vyber všechny neděle
SELECT * 
FROM tabulka1
WHERE "den v týdnu" = 'ne';

 řádek |          datum a čas          | rok  | čtvrtletí | týden | měsíc | den v týdnu | den | hodina | minuta | vteřina 
-------+-------------------------------+------+-----------+-------+-------+-------------+-----+--------+--------+---------
     1 | 2024-12-15 17:51:57.558223+00 | 2024 |         4 |    50 |    12 | ne          |  15 |     17 |     51 |      58
     7 | 2024-11-24 19:04:51.990223+00 | 2024 |         4 |    47 |    11 | ne          |  24 |     19 |      4 |      52
     9 | 2024-11-24 11:13:19.964623+00 | 2024 |         4 |    47 |    11 | ne          |  24 |     11 |     13 |      20
    20 | 2024-12-01 08:36:51.827023+00 | 2024 |         4 |    48 |    12 | ne          |   1 |      8 |     36 |      52
    31 | 2024-11-24 13:34:31.830223+00 | 2024 |         4 |    47 |    11 | ne          |  24 |     13 |     34 |      32
    32 | 2024-12-01 05:37:02.799823+00 | 2024 |         4 |    48 |    12 | ne          |   1 |      5 |     37 |       3
    36 | 2024-12-29 08:04:27.308623+00 | 2024 |         4 |    52 |    12 | ne          |  29 |      8 |      4 |      27
    46 | 2024-10-20 20:42:19.369423+00 | 2024 |         4 |    42 |    10 | ne          |  20 |     20 |     42 |      19
    47 | 2024-11-24 19:58:30.822223+00 | 2024 |         4 |    47 |    11 | ne          |  24 |     19 |     58 |      31
    50 | 2024-11-24 08:07:22.009423+00 | 2024 |         4 |    47 |    11 | ne          |  24 |      8 |      7 |      22
    55 | 2024-10-06 12:04:21.030223+00 | 2024 |         4 |    40 |    10 | ne          |   6 |     12 |      4 |      21
    56 | 2024-11-10 19:06:10.009423+00 | 2024 |         4 |    45 |    11 | ne          |  10 |     19 |      6 |      10
    77 | 2024-11-17 11:13:38.799823+00 | 2024 |         4 |    46 |    11 | ne          |  17 |     11 |     13 |      39
    78 | 2024-11-24 06:18:40.105423+00 | 2024 |         4 |    47 |    11 | ne          |  24 |      6 |     18 |      40
    81 | 2024-10-06 06:17:58.374223+00 | 2024 |         4 |    40 |    10 | ne          |   6 |      6 |     17 |      58
    87 | 2024-12-08 10:47:58.547023+00 | 2024 |         4 |    49 |    12 | ne          |   8 |     10 |     47 |      59
    88 | 2024-12-22 00:15:22.249423+00 | 2024 |         4 |    51 |    12 | ne          |  22 |      0 |     15 |      22
    92 | 2024-10-06 07:02:34.787023+00 | 2024 |         4 |    40 |    10 | ne          |   6 |      7 |      2 |      35
    93 | 2024-10-20 03:12:27.894223+00 | 2024 |         4 |    42 |    10 | ne          |  20 |      3 |     12 |      28
    94 | 2024-10-06 17:04:13.830223+00 | 2024 |         4 |    40 |    10 | ne          |   6 |     17 |      4 |      14
    96 | 2024-12-22 21:43:21.433423+00 | 2024 |         4 |    51 |    12 | ne          |  22 |     21 |     43 |      21

Všimněte si, že se pro sloupec den v týdnu místo čísel použily zkrácené názvy dnů. Je to pro uživatele přehlednější.

ISODOW vrací čísla dnů, 1 - pondělí až 7 - neděle.

Existuje i parametr DOW, který vrací čísla 0 - pondělí až 6 - neděle.

Snadné filtrování

Když jsou jednotlivé části rozdělené, filtrování půjde jako po másle:

-- Vyber všechny neděle, ve 4. kvartálu, v roce 2024
SELECT * 
FROM tabulka1
WHERE "den v týdnu" = 'ne'
  AND "čtvrtletí" = 4
  AND "rok" = 2024
;

Výhody

Jaké jsou výhody rozdělení sloupců typu DATE a TIMESTAMP na jednotlivé části?

• Lepší čitelnost, protože jednotlivé části jsou v samostatných sloupcích.
• Snadnější filtrování na jednotlivé sloupce a díky tomu přehlednější WHERE podmínky.
• Jednodušší agregace pro snadné přehledy podle určitého období

Nevýhodou přidání dalších sloupců do tabulky může být fakt, že bude tabulka zabírat více místa. To platí zejména pro rozsáhlé tabulky, které mají mnoho záznamů.

Pokud není možné měnit strukturu tabulky, lze místo toho vytvořit pohled (VIEW).