Skip to main content

3.3 Práca s časovými údajmi

Vo výrazoch a funkciách pre dátum a čas sú rozdiely medzi jednotlivými SQL dialektami značné a treba uznať, že Oracle SQL má najbohatšie možnosti. Budeme sa však snažiť používať tie výrazy, ktoré fungujú univerzálne.

Aktuálny čas

Veľmi dôležité sú výrazy pre aktuálny čas:

  • CURRENT_DATE: vráti aktuálny dátum;
  • CURRENT_TIMESTAMP: vráti aktuálny dátum a čas (pozor, rôzne formáty v rôznych SQL dialektoch).
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;

Nezabúdajme na časové zóny! Server vracia čas v tej časovej zóne, pre ktorú je nakonfigurovaný. Pokiaľ v systéme pracujeme s časmi rôznych časových zón, odporúčaný štandard je pracovať vždy s neutrálnou časovou zónou (UTC / GMT) a do cieľovej časovej zóny údaj previesť až na výstupe v aplikácii.

Formát dátumu a času

Dátum a čas sa zapisuje a zobrazuje ako textový reťazec v štandardnom tvare:

  • dátum v tvare RRRR-MM-DD, napríklad '2025-02-04';
  • môže byť pridaný čas v tvare HH:MM, prípadne HH:MM:SS, napríklad '2025-02-04 08:05:30' - ak čas neuvedieme, použije sa 00:00:00;
  • doplnená môže byť aj časová zóna v tvare +ZZ, napríklad '2025-02-04 08:05:30+01' - ak časovú zónu neuvedieme, použije sa nastavená časová zóna servera.

Aritmetika časových údajov

Ak chceme k dátumu pripočítať dni, v Oracle SQL a PostgreSQL stačí pripočítať ich počet, no budeme používať univerzálny zápis pomocou INTERVAL:

  • {čas} + INTERVAL '{počet}' {pole}: hodnota počet musí byť v podobe textového reťazca a hodnota pole je kľúčové slovo, môže byť napríklad DAY, MONTH, YEAR, HOUR, MINUTE, SECOND.

Čas môžeme nielen pripočítavať, ale aj odpočítavať, napríklad:

SELECT CURRENT_TIMESTAMP + INTERVAL '1' DAY - INTERVAL '2' HOUR + INTERVAL '10' MINUTE + INTERVAL '30' SECOND;

Rozdiel dvoch časových údajov

Kým v Oracle SQL a PostgreSQL môžeme zistiť počet dní medzi dvoma dátumami prostým odpočítaním (napríklad CURRENT_DATE - hire_date), v MySQL/MariaDB musíme využiť funkciu:

  • DATEDIFF({dátum 1}, {dátum 2})

Pre pohyb v kalendári môžeme využiť aj ďalšie funkcie, no len v niektorých dialektoch SQL:

  • LAST_DAY({dátum}) vráti posledný deň mesiaca, nefunguje v PostgreSQL;
  • NEXT_DAY({dátum}, {deň}) vráti ďalší dátum zadaného dňa v týždni - len v Oracle SQL;
  • MONTHS_BETWEEN({dátum 1}, {dátum 2}) vráti počet mesiacov medzi dvoma dátumami - len v Oracle SQL.

Príklad - vypíše počet dní do konca mesiaca (MariaDB):

SELECT DATEDIFF(LAST_DAY(CURRENT_DATE), CURRENT_DATE);

Extrakcia časových údajov

Pri práci s časovými údajmi, a to predovšetkým pri ich agregácii podľa času a pri tvorbe grafov, často potrebujeme extrahovať z časového údaju niektorú časť. Umožňujú nám to ďalšie funkcie…

  • EXTRACT({pole} FROM {čas}): z uvedeného času / dátumu získa uvedené pole,
    • pole je kľúčové slovo: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEK;
  • alternatíva v PostgreSQL: DATE_PART({pole}, {čas}),
    • pole sa zapisuje ako text, napríklad 'day', 'month', 'year', 'hour', 'minute', 'second', 'week', 'dow' (deň týždňa, nedeľa je 0), 'isodow' (deň týždňa, nedeľa je 7);
  • alternatívy v MariaDB:
    • DAY({čas}), MONTH({čas}), YEAR({čas}), HOUR({čas}), MINUTE({čas}), SECOND({čas}), WEEK({čas}),
    • WEEKDAY({čas}) - deň týždňa, pričom pondelok je 0 a nedeľa je 6.
-- zistí aktuálny mesiac a rok:
SELECT EXTRACT(MONTH FROM CURRENT_DATE), EXTRACT(YEAR FROM CURRENT_DATE);
SELECT DATE_PART('month', CURRENT_DATE), DATE_PART('year', CURRENT_DATE); -- len PostgreSQL
SELECT MONTH(CURRENT_DATE), YEAR(CURRENT_DATE); -- len MariaDB

-- zistí aktuálny deň v týždni:
SELECT DATE_PART('dow', CURRENT_DATE); -- len PostgreSQL (nedeľa je 0)
SELECT WEEKDAY(CURRENT_DATE); -- len MariaDB (nedeľa je 6)

Zaokrúhľovanie časových údajov

Niekedy potrebujeme časový údaj „zaokrúhliť“ - ak nás napríklad zaujíma len hodina, odstránime minúty a sekundy:

  • PostgreSQL: DATE_TRUNC({pole}, {čas}): uvedený čas / dátum zaokrúhli podľa uvedeného poľa,
    • pole sa zapisuje ako text, napríklad 'day', 'month', 'year', 'hour', 'minute', 'second', 'week';
  • MySQL/MariaDB: je možné využiť funkciu DATE_FORMAT() a výpočty s ďalšími funkciami;
  • Oracle SQL: využijeme bežné funkcie ROUND() a TRUNC(), syntax je rovnaká ako DATE_TRUNC() v PostgreSQL.
-- variant PostgreSQL
-- zistí, kedy začala aktuálna hodina:
SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP);
-- zistí dátum stredy tohto týždňa:
SELECT DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '2' DAY;
-- zistí posledný deň v mesiaci
SELECT DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1' MONTH - INTERVAL '1' DAY;
-- variant MariaDB
-- zistí, kedy začala aktuálna hodina:
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %H:00:00');
-- zistí dátum stredy tohto týždňa:
SELECT CURRENT_DATE + INTERVAL ('2' - WEEKDAY(CURRENT_DATE)) DAY;
-- zistí posledný deň v mesiaci
SELECT LAST_DAY(CURRENT_DATE);

Zaradenie časových údajov do časových intervalov

Ešte zaujímavejšie je časový údaj „zaokrúhliť“ na akékoľvek menšie časové intervaly. Teda nielen celé hodiny, ale aj štvrťhodiny, či 5-minútové intervaly:

  • PostgreSQL: DATE_BIN({interval}, {čas}): vylepšenie DATE_TRUNC, pre uvedený čas / dátum zistí začiatok jeho intervalu,
    • parameter interval je v podobe textového reťazca, napríklad '15 seconds',
    • je možné pridať ešte 3. parameter určujúci počiatok, od ktorého sa má počítať;
  • MySQL/MariaDB: časový údaj je potrebné konvertovať na timestamp (počet sekúnd) funkciou UNIX_TIMESTAMP({čas}), tomu odstrániť požadované detaily (delením a spätným násobením) a následne previesť späť na časový údaj funkciou FROM_UNIXTIME().
-- zistí, kedy začal posledný 5-minútový interval:
-- PostgreSQL: elegantná funkcia DATE_BIN
SELECT DATE_BIN('5 minutes', CURRENT_TIMESTAMP, '2000-01-01');
-- MariaDB: matematický prepočet cez sekundy
SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) / 300) * 300);

Načo je dobré „zaokrúhľovať“ čas na nejaké intervaly?

Je to dôležité napríklad pri kreslení grafov. Pokiaľ nám senzory odovzdávajú výsledky napríklad každých 5 sekúnd a chceme graf napríklad za posledných 24 hodín, v grafe by bolo cca 17 000 hodnôt. Preto hodnoty zhrnieme do nejakých intervalov a pre každý interval zobrazíme len jeden bod na grafe - typicky priemer, ale môžeme aj minimum a maximum.

Back to top