MySQL TIMESTAMP vysvětleno: časová pásma, rozdíly oproti DATETIME a problém roku 2038

1. Co je MySQL TIMESTAMP?

Datový typ TIMESTAMP v MySQL je určen k ukládání konkrétního okamžiku v UTC (Coordinated Universal Time) a automaticky provádí konverzi časových pásem při ukládání i načítání dat. Tento typ dokáže pracovat s daty a časy v rozmezí od 1. ledna 1970 do 19. ledna 2038. Při ukládání dat do databáze používá TIMESTAMP aktuální časové pásmo a při načítání je automaticky převeden podle systémového časového pásma.

Rozdíly mezi TIMESTAMP a DATETIME

Datový typ DATETIME se často srovnává s TIMESTAMP. DATETIME ukládá datum a čas „tak, jak jsou“, takže uložená data nejsou ovlivněna časovými pásmy. Naopak TIMESTAMP je při uložení převeden na UTC a při načtení zpět na systémové časové pásmo, což pomáhá předcházet posunům času napříč prostředími.

Například TIMESTAMP je zvláště užitečný při migracích systémů nebo při práci s databázemi napříč více časovými pásmy. DATETIME podporuje širší rozsah – od roku 1000 do 9999 – a proto se často používá k vyhnutí se problému roku 2038.

Příklad použití TIMESTAMP

Tabulku můžete vytvořit s TIMESTAMP následovně.

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

V tomto příkladu sloupec event_time automaticky ukládá aktuální čas při vložení záznamu a přepisuje tuto hodnotu při každé aktualizaci záznamu.

2. Základní použití TIMESTAMP

Při používání TIMESTAMP v MySQL je důležité pochopit základní způsoby vkládání a načítání hodnot. Níže jsou uvedeny několik běžných přístupů k práci s daty typu TIMESTAMP.

Vložení data a času

Při vkládání dat do sloupce TIMESTAMP obvykle zadáváte datum a čas ve formátu řetězce. Datum je reprezentováno jako „YYYY-MM-DD“ a čas jako „hh:mm:ss“.

INSERT INTO events (event_time) VALUES ('2023-10-01 12:30:00');

Tento SQL příkaz vloží 12:30:00 dne 1. října 2023 do sloupce event_time.

Vložení aktuálního času

Pomocí funkce NOW() v MySQL můžete snadno získat aktuální datum a čas. Tato funkce vrací aktuální datum a čas podle systémového časového pásma a můžete ji přímo vložit do sloupce TIMESTAMP.

INSERT INTO events (event_time) VALUES (NOW());

V tomto příkladu je automaticky vložen aktuální čas v okamžiku provedení SQL příkazu.

Použití funkce automatické aktualizace

Pokud pro sloupec TIMESTAMP specifikujete ON UPDATE CURRENT_TIMESTAMP, čas aktualizace je automaticky zaznamenán při každé změně záznamu.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

V této tabulce je order_time nastaven na aktuální čas při vytvoření záznamu a při každé jeho aktualizaci se automaticky aktualizuje.

3. Práce s TIMESTAMP a časovými pásmy

Jednou z největších výhod TIMESTAMP je zpracování časových pásem. Uložená data jsou vždy převedena na UTC a při načtení z databáze jsou znovu převedena tak, aby odpovídala systémovému časovému pásmu.

Jak zkontrolovat nastavení časového pásma

V MySQL můžete nastavit časové pásmo na úrovni serveru nebo relace. Nastavení časového pásma můžete zkontrolovat pomocí příkazu SHOW VARIABLES.

SHOW VARIABLES LIKE 'time_zone';

Tento příkaz vrátí aktuálně nakonfigurované časové pásmo databáze. Pro změnu časového pásma použijte následující příkaz.

SET time_zone = '+09:00';

Rozdíly v časových pásmech mezi TIMESTAMP a DATETIME

Typ DATETIME ukládá datum a čas bez ohledu na časová pásma, zatímco typ TIMESTAMP je při uložení převeden na UTC. Proto je v prostředích, kde koexistují různá časová pásma, TIMESTAMP často lepší volbou.

4. Problém roku 2038 a jeho dopad

The Year 2038 problem is caused by the limitation of the TIMESTAMP type on 32-bit systems. MySQL’s TIMESTAMP type is based on the number of seconds since 00:00:00 UTC on January 1, 1970. When it exceeds 03:14:07 UTC on January 19, 2038, this value overflows.

How to Avoid the Year 2038 Problem

To avoid this problem, it is recommended to use a 64-bit system or the wider-range DATETIME type. DATETIME can handle dates and times from year 1000 to 9999, so it can be used safely beyond 2038.

You can also avoid this issue by upgrading your system. Since 64-bit systems do not have the Year 2038 limitation, it is important to consider upgrading your database and applications.

5. Practical Use Cases for the TIMESTAMP Type

The MySQL TIMESTAMP type is not only used for storing basic date and time values, but also supports a variety of practical patterns such as automatically inserting or updating the current time. Here are some common advanced use cases.

Automatically Insert the Current Time

When defining a TIMESTAMP column, you can set CURRENT_TIMESTAMP as the default value so that the current date and time is automatically inserted whenever a new record is created. For example, to create a table that automatically records when an order occurs, you can do the following.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Automatically Record the Update Time

By specifying ON UPDATE CURRENT_TIMESTAMP, the update time is automatically recorded every time a record is updated. This makes it easy to manage update history automatically.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Using Multiple TIMESTAMP Columns

In MySQL, you can include multiple TIMESTAMP columns in a table, but by default only one column can have CURRENT_TIMESTAMP as its default value. If you want to manage multiple timestamps automatically, explicitly set values for the other columns or use the DATETIME type.

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

6. Important Notes When Using the TIMESTAMP Type

When using the TIMESTAMP type, there are several important caveats to understand. Knowing these helps prevent unexpected inconsistencies and errors.

NULL Constraints and Default Values

By default, a NOT NULL constraint is applied to TIMESTAMP columns. In other words, if you want to allow NULL values, you must explicitly specify DEFAULT NULL.

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP DEFAULT NULL
);

You can also specify DEFAULT 0 to set an invalid datetime value of 0000-00-00 00:00:00 by default. However, this is not recommended. In MySQL’s strict SQL mode, this invalid datetime can cause errors.

The Problem with 0000-00-00 00:00:00

Some MySQL versions support 0000-00-00 00:00:00 as an invalid datetime value, but it can cause operational issues in real‑world systems. In particular, systems that prioritize data integrity should avoid such invalid values. Instead, it is recommended to use NULL or an appropriate default value.

CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL
);

Impact of the System Time Zone

Because the TIMESTAMP type is stored after conversion to UTC, you should be careful when migrating databases across different time zones. If the server time zone setting changes, the retrieved times may become unintended values. It’s important to manage time zones accurately.

SET time_zone = 'Asia/Tokyo';

Tento příkaz nastaví časovou zónu databáze na Tokio a zajistí přesné řízení konverzí z UTC.

7. Shrnutí a doporučení

Typ TIMESTAMP je silný nástroj pro efektivní řízení dat a časů v MySQL. Zejména automatická konverze s časovými zónami a automatické záznamní času při vytváření/aktualizaci jsou velmi pohodlné. Nicméně je důležité pochopit omezení a varování, jako je problém roku 2038 a způsob zpracování hodnot NULL.

Kdy použít TIMESTAMP

• Když potřebujete chování auto-aktualizace , je TIMESTAMP ideální—zejména pokud chcete automaticky zaznamenat časovou značku pokaždé, když je záznam aktualizován.
• V systémech, které musí zohledňovat časové zóny , je chování konverze založené na UTC u TIMESTAMP užitečné.
• Na druhé straně, pokud potřebujete odolný rozsah do budoucnosti nebo musíte zpracovávat data mimo podporovaný rozsah (po roce 2038), zvažte použití DATETIME .

Nakonec vyberte mezi TIMESTAMP a DATETIME na základě požadavků vašeho systému, aby byla zajištěna integrita dat a udržitelnost.

8. Často kladené otázky (FAQ)

Otázky a problémy související s TIMESTAMP v MySQL jsou mezi vývojáři běžné, takže tato sekce shrnuje často kladené otázky. Tyto FAQ poskytují užitečné tipy a řešení pro správné zpracování TIMESTAMP .

Jak bych měl vybrat mezi TIMESTAMP a DATETIME ?

TIMESTAMP automaticky konvertuje hodnoty na základě časových zón pomocí UTC, takže je vhodný pro aplikace a systémy, které musí zohledňovat více časových zón. Podporuje také automatické ukládání dat/časových hodnot při vytváření nebo aktualizaci záznamů. Naproti tomu DATETIME ukládá hodnoty „jak jsou“, takže je lepší, když potřebujete konzistentní řízení dat/času bez konverze časových zón.

Je pravda, že TIMESTAMP nelze použít po roce 2038 ?

Ano. Problém roku 2038 se týká 32bitového typu TIMESTAMP . Protože je založen na počtu sekund od 1. ledna 1970, nemůže reprezentovat data/čas po 19. lednu 2038. Aby se tomu vyhnuli, doporučuje se migrace na 64bitový systém nebo použití typu DATETIME .

Jak mohu povolit hodnoty NULL ve sloupci TIMESTAMP ?

Aby byly v sloupci TIMESTAMP povoleny hodnoty NULL, musíte explicitně specifikovat DEFAULT NULL , jak je ukázáno níže.

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP DEFAULT NULL
);

S tímto nastavením, pokud vložíte záznam bez specifikace data/času, uloží se hodnota NULL .

Pokud změním nastavení časové zóny, ovlivní to existující data TIMESTAMP ?

Protože hodnoty TIMESTAMP jsou při ukládání konvertovány na UTC, změna nastavení časové zóny ovlivní způsob, jakým se data zobrazí při načítání. Základní data jsou stále uložena v UTC, ale budou konvertována na základě nové časové zóny, což změní načtené časové hodnoty. Aby data zůstala konzistentní, je důležité standardizovat nastavení časových zón v celém systému.

Pokud použiji CURRENT_TIMESTAMP , mohu stále vložit specifické datum/čas ?

CURRENT_TIMESTAMP automaticky vloží aktuální čas při vložení záznamu, ale můžete stále explicitně vložit specifické datum/čas pomocí NOW() nebo literálu řetězce.

INSERT INTO events (event_time) VALUES ('2023-10-01 12:30:00');

Tímto způsobem můžete ručně vložit data/čas i při použití CURRENT_TIMESTAMP .