Průvodce daty a časy v MySQL: datové typy, funkce, formátování a rozsahové dotazy

1. Úvod

MySQL je široce používaný systém pro správu databází pro budování webových aplikací a databází a správná manipulace s daty o datech je obzvláště důležitá. Například data související s daty se objevují v mnoha scénářích: správa příspěvků na blogu, sledování historie prodejů produktů a ukládání historie přihlášení uživatelů. Správnou správou dat o datech můžete zpracovávat data efektivně a poskytovat uživatelům přesné informace.

V tomto průvodci podrobně vysvětlíme vše od základních typů dat o datech/času a jak používat funkce dat v MySQL po výpočty založené na datech a dotazy na rozsahy. Tento obsah je určen pro začátečníky až středně pokročilé uživatele a zahrneme praktické příklady dotazů založené na reálných použitích – což ho činí užitečným pro každodenní práci.

Přečtením tohoto článku budete schopni následujícího:

• Pochopit charakteristiky typů dat o datech/času v MySQL a vybrat vhodný typ pro vaše data.
• Používat funkce dat k snadnému získání a manipulaci s aktuálním datem, minulými daty a budoucími daty.
• Extrahovat data pro specifická období prováděním vyhledávání a porovnání rozsahů dat.

Nyní se podívejme na základy dat MySQL počínaje následující sekcí.

2. Přehled typů dat o datech/času v MySQL

Při správě dat v MySQL je důležité vybrat správný typ dat o datech/času v závislosti na datech a použití. MySQL poskytuje více typů dat pro manipulaci s daty a časy, každý s různými charakteristikami a použitím. V této sekci podrobně vysvětlíme hlavní typy dat/času a jejich typická použití.

DATE

Typ DATE ukládá pouze datum (rok, měsíc, den) a nezahrnuje časovou složku. Podporovaný rozsah je od „1000-01-01“ do „9999-12-31“, takže může zpracovávat data od roku 1000 do 9999. Je vhodný pro informace pouze o datech, kde čas není relevantní, jako narozeniny nebo výročí.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Typ TIME ukládá čas (hodiny, minuty, sekundy). Podporovaný rozsah je od „-838:59:59“ do „838:59:59“. Protože může reprezentovat negativní časy, může být také použit k vyjádření rozdílů časů. Je užitečný pro záznam pracovních hodin nebo trvání úkolů.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Typ DATETIME ukládá datum i čas. Podporovaný rozsah je od „1000-01-01 00:00:00“ do „9999-12-31 23:59:59“. Nezávisí na časových pásmech a můžete získat uloženou hodnotu přesně tak, jak byla uložena. Je vhodný pro záznam časových razítek minulých událostí nebo budoucích plánů.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Typ TIMESTAMP ukládá datum a čas a automaticky konvertuje hodnoty na základě časového pásma serveru. Podporovaný rozsah je od „1970-01-01 00:00:01 UTC“ do „2038-01-19 03:14:07 UTC“. Je kompatibilní s Unix epoch time a je vhodný pro záznam časových razítek a historie změn. Také proto, že TIMESTAMP může nastavit aktuální čas jako výchozí, je pohodlný pro automatické záznam vytvoření a aktualizace časů.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Typ YEAR ukládá pouze rok. Podporovaný rozsah je od „1901“ do „2155“. Je užitečný, když chcete reprezentovat specifický rok, jako rok výroby nebo založení.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Porovnání a použití každého typu dat/času

Shrnutí

MySQL date/time data types should be chosen based on the characteristics of your data and how you plan to use it. In the next section, we’ll take a closer look at the differences between DATETIME and TIMESTAMP, which are especially easy to confuse.

3. Differences Between DATETIME and TIMESTAMP

When working with dates and times in MySQL, DATETIME and TIMESTAMP are commonly used, but there are important differences between them. Both store year, month, day, hour, minute, and second, but they differ in how they handle time zones and the time range they can store. You should choose based on your use case. In this section, we’ll explain the differences and characteristics of DATETIME and TIMESTAMP in detail.

Characteristics of DATETIME

• Not dependent on time zones : The DATETIME type is not affected by the server time zone setting. You can retrieve the stored value exactly as saved.
• Range : It supports from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
• Use cases : Suitable for data you want to store without relying on any particular time zone, such as historical events or future schedules.

Example: Storing an event date/time

For example, if you want to keep a “universal” date/time value as-is, DATETIME is a good choice. The following example records an event scheduled at a specific date and time.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

In this table, the date/time stored in event_datetime is not affected by time zones, and the value is retrieved exactly as stored.

Characteristics of TIMESTAMP

• Dependent on time zones : The TIMESTAMP type is stored and retrieved based on the server time zone. When moving data between servers in different time zones, conversion will occur accordingly.
• Range : It supports from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (based on the Unix time range).
• Auto-update support : Using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP , MySQL can automatically record the current timestamp when inserting or updating data.
• Use cases : Best for automatically tracking creation or update time, such as when you want a timestamp to change whenever a record is updated.

Example: Storing created and updated times for posts

This example uses the auto-update feature of TIMESTAMP to record creation and update times for blog posts.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

With this setting, created_at is recorded when the post is first created, and updated_at is automatically updated every time the post is edited.

Comparison Table: DATETIME vs TIMESTAMP

How to Choose

• Choose DATETIME if you don’t want time zone effects : For example, if you want to store an event time as a fixed local time for a specific country/region, DATETIME can be appropriate.
• Choose TIMESTAMP if you need auto-update or time zone handling : For example, if you want to automatically record when database rows are updated, TIMESTAMP is convenient.

Summary

DATETIME and TIMESTAMP have different characteristics. By choosing the right one for your purpose, you can manage date/time data more efficiently. In the next section, we’ll look at the essential date functions in MySQL.

4. MySQL Date/Time Functions Basics

MySQL provides many functions for working with dates and times—from retrieving the current date/time to adding or subtracting intervals. These functions are useful for database administration and analysis. In this section, we’ll explain the basics of MySQL date/time functions and their typical use cases.

Functions to Get the Current Date/Time

Tři běžné funkce pro získání aktuálního data a času v MySQL jsou NOW(), CURDATE() a CURTIME().

NOW()

Funkce NOW() vrací aktuální datum a čas ve formátu YYYY-MM-DD HH:MM:SS. Je užitečná pro logování a časové razítkování záznamů.

SELECT NOW(); -- Get the current date and time

CURDATE()

Funkce CURDATE() vrací aktuální datum ve formátu YYYY-MM-DD. Nezahrnuje čas, takže je vhodná, když potřebujete jen datum.

SELECT CURDATE(); -- Get the current date

CURTIME()

Funkce CURTIME() vrací aktuální čas ve formátu HH:MM:SS. Použijte ji, když potřebujete jen čas bez data.

SELECT CURTIME(); -- Get the current time

Funkce pro přidávání/odečítání dat

Pro přidání nebo odečtení intervalů od data použijte DATE_ADD() a DATE_SUB(). Tyto funkce usnadňují výpočet budoucích nebo minulých dat.

DATE_ADD()

Funkce DATE_ADD() přidá zadaný interval k datu. Například je užitečná pro získání data o 7 dní později nebo o 1 měsíc později.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

Funkce DATE_SUB() odečte zadaný interval od data. Použijte ji k výpočtu minulých dat.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Funkce pro výpočet rozdílů mezi daty

Pro výpočet rozdílu mezi dvěma daty je DATEDIFF() praktická. Například můžete vypočítat, kolik dní uplynulo od konkrétního data, nebo počet dní mezi dvěma daty.

DATEDIFF()

Funkce DATEDIFF() vrací rozdíl mezi dvěma daty ve dnech. Je užitečná, když chcete potvrdit počet dní od počátečního data do koncového data.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Další užitečné funkce pro datum

MySQL poskytuje další užitečné funkce pro práci s daty.

EXTRACT()

Funkce EXTRACT() extrahuje konkrétní část (rok, měsíc, den atd.) z data. Je užitečná, když potřebujete jen část data.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

Funkce DATE_FORMAT() zobrazuje datum ve zvoleném formátu. Je užitečná, když chcete zobrazit data v konkrétním formátu (například japonské formátování).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Metody formátování a konverze dat

V MySQL můžete změnit způsob, jakým jsou data zobrazována, na čitelnější formát, nebo převést řetězce s daty na datové typy. To vám umožní flexibilně řídit formáty zobrazení a konzistentně spravovat data i když vstupy přicházejí v různých formátech. V této sekci vysvětlíme hlavní funkce používané pro formátování a konverzi dat.

Formátování dat pomocí funkce DATE_FORMAT()

Pomocí DATE_FORMAT() můžete zobrazit hodnoty data ve zvoleném formátu. To je zvláště užitečné, když potřebujete jiný zobrazovací formát než standardní, například japonský formát „YYYY年MM月DD日“.

Možnosti formátu

S DATE_FORMAT() můžete použít následující možnosti formátu:

• %Y : 4ciferný rok
• %y : 2ciferný rok
• %m : 2ciferný měsíc (01–12)
• %d : 2ciferný den (01–31)
• %H : 2ciferná hodina (00–23)
• %i : 2ciferná minuta (00–59)
• %s : 2ciferná sekunda (00–59)

Příklad použití

Například pro zobrazení data 2023-01-01 jako „2023年01月01日“ můžete napsat následující:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Můžete také zobrazit datum a čas s lomítky, například „2023/01/01 12:30:45“.

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Převod řetězců na data s STR_TO_DATE()

Funkce STR_TO_DATE() převádí řetězec data na typ data MySQL. Například je užitečná, když chcete zacházet s řetězcem jako „2023年01月01日“ jako hodnotou DATE.

Příklad použití

Aby se řetězec „2023-01-01“ převedl na hodnotu DATE, napište následující:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Aby se japonský styl řetězce jako „2023年01月01日“ převedl na datum, zadejte odpovídající formát:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Příklady převodu dat z různých formátů

V reálném použití se formáty vstupních dat mohou lišit, takže možná budete muset převádět z různých formátů. Níže je několik příkladů.

1. Převod „YYYY/MM/DD“ na hodnotu DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Převod „MM-DD-YYYY“ na hodnotu DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Tímto způsobem, i když jsou data zadána v různých formátech, můžete je ukládat a spravovat konzistentně v sjednoceném formátu data.

Rozdíly mezi DATE_FORMAT a STR_TO_DATE a kdy kterou použít

• DATE_FORMAT() : Používá se k změně formátu zobrazení existujících hodnot data. Protože změna ovlivňuje pouze zobrazení, ukládaná data samotná se nemění.
• STR_TO_DATE() : Používá se k převodu řetězce data na hodnotu MySQL DATE nebo DATETIME. Tím se změní typ ukládaných dat a usnadní zpracování dat s jinými funkcemi a dotazy MySQL.

Shrnutí

Pomocí DATE_FORMAT() a STR_TO_DATE() můžete flexibilně spravovat, jak se data zobrazují a ukládají. To umožňuje flexibilnější zpracování vstupů od uživatelů nebo systému při zachování konzistentní správy dat. V další části vysvětlíme výpočty a porovnání dat a podrobněji se podíváme na to, jak počítat a porovnávat periody pomocí datových dat.

6. Výpočty a porovnání dat

MySQL poskytuje několik pohodlných funkcí pro výpočty a porovnání dat. Tyto funkce umožňují extrahovat data pro specifické periody nebo počítat rozdíly dat pro analýzu. V této části představíme hlavní funkce používané pro výpočty a porovnání dat spolu s jejich použitím.

Funkce pro výpočet rozdílů dat: DATEDIFF()

Funkce DATEDIFF() vrací rozdíl mezi dvěma daty v „dnech“. Je užitečná, když chcete zkontrolovat uplynulé dny mezi daty nebo určit, kolik dní uplynulo od minulé události do dneška.

Příklad použití

Například pro výpočet počtu dní od 1. ledna 2023 do 10. ledna 2023 napište následující:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

V tomto příkladu je rozdíl od počátečního data do koncového data 9 dní, takže výsledek je 9.

Výpočet rozdílů podle jednotky s TIMESTAMPDIFF()

Funkce TIMESTAMPDIFF() počítá rozdíl mezi dvěma daty nebo datetimes v určené jednotce (rok, měsíc, den, hodina, minuta, sekunda). Například můžete počítat rozdíly v „měsících“ nebo „hodinách“ podle potřeby.

Příklad použití

1. Výpočet rozdílu v měsících

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Výpočet rozdílu v hodinách

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Porovnávání dat

Porovnávání dat se provádí pomocí standardních porovnávacích operátorů MySQL (<, >, <=, >=, =). To umožňuje extrahovat data v určitém období nebo zahrnout minulé/budoucí data jako podmínky.

Příklad použití

Například pro extrakci dat „od 1. ledna 2023 a dále“ napište následující:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Určování rozsahu pomocí BETWEEN

Operátor BETWEEN vám umožňuje specifikovat rozsah dat pro načtení dat. Je užitečný, když chcete extrahovat záznamy, které spadají do určitého období.

Příklad použití

Například pro načtení dat od 1. ledna 2023 do 31. ledna 2023 napište následující:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Výpočty dat s ADDDATE() a SUBDATE()

Můžete použít ADDDATE() a SUBDATE() k přidání nebo odebrání počtu dní od data. Tyto funkce jsou pohodlné pro výpočet budoucích nebo minulých dat.

Příklad použití

1. Přidat 10 dní k datu

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Odebrat 10 dní od data

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Shrnutí

Pomocí funkcí MySQL pro výpočet a porovnání dat můžete efektivně extrahovat data pro specifická období a analyzovat data podle časových rozsahů. V další sekci se podrobněji podíváme na pokročilé téma „vyhledávání v rozsahu dat“.