MySQL TIMESTAMP vs DATETIME: täielik juhend, kasutusnäited ja 2038. aasta probleem

1. Mis on MySQL timestamp?

MySQL-is olev TIMESTAMP andmetüüp salvestab kindla aja UTC (Universaalne koordineeritud aeg) formaadis ning arvestab automaatselt ajavööndit salvestamisel ja andmete pärimisel. See andmetüüp suudab hallata kuupäevi ja kellaaegu vahemikus 1. jaanuar 1970 kuni 19. jaanuar 2038. Kui andmed salvestatakse, kasutab TIMESTAMP praegust ajavööndit ning seejärel teisendab väärtuse süsteemi ajavööndi põhjal tagasi pärimisel.

TIMESTAMP vs DATETIME

TIMESTAMP-i võrreldakse sageli DATETIME andmetüübiga. DATETIME salvestab kuupäeva ja kellaaja otse ega arvesta ajavööndiga. Seevastu TIMESTAMP teisendatakse salvestamisel UTC-ks ja kuvatakse süsteemi ajavööndi järgi, mis aitab vältida nihkeid eri ajavööndite vahel.

Näiteks on TIMESTAMP eriti kasulik, kui süsteem liigub uude serverisse või kui andmebaasi kasutatakse mitmes ajavööndis. DATETIME sobib juhtudeks, kus on vaja käsitleda laiemaid kuupäevavahemikke (1000–9999), näiteks 2038. aasta probleemi vältimiseks.

TIMESTAMP-i kasutusnäide

Tabeli saab luua järgmiselt:

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

Selles näites salvestatakse event_time veergu automaatselt hetkeaeg, kui rida lisatakse, ja uuendatakse iga muudatuse korral.

2. TIMESTAMP-i põhiline kasutamine

MySQL-is TIMESTAMP-i kasutamisel on oluline teada, kuidas teha lihtsaid sisestusi ja päringuid. Allpool on toodud mõned meetodid andmete töötlemiseks.

Kuupäeva ja kellaaja sisestamine

Andmete sisestamiseks TIMESTAMP veergu kasutatakse tavaliselt stringi formaadis väärtusi: kuupäev „YYYY-MM-DD” ja kellaaeg „hh:mm:ss”.

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

Selles näites sisestatakse 1. oktoober 2023 kell 12:30 veergu event_time.

Praeguse aja sisestamine

MySQL-i NOW() funktsioon tagastab süsteemi ajavööndi järgi praeguse kuupäeva ja kellaaja. Seda saab otse sisestada TIMESTAMP-i veergu.

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

See lisab SQL-i käivitamise hetke aja.

Automaatse uuendamise funktsioon

Kui määrata ON UPDATE CURRENT_TIMESTAMP, uuendatakse TIMESTAMP veerg iga rea muutmise korral automaatselt.

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

Selles tabelis täidetakse order_time uue rea loomisel ja uuendatakse iga muudatuse korral.

3. TIMESTAMP ja ajavööndite käsitlemine

Üks TIMESTAMP-i olulisemaid omadusi on ajavööndite arvestamine. Andmed salvestatakse alati UTC-na ning pärimisel teisendatakse need süsteemi ajavööndi järgi.

Ajavööndi seadistuse kontrollimine

MySQL-is saab ajavööndi seadistada serveri või sessiooni tasandil. Seadistust saab kontrollida käsuga SHOW VARIABLES.

SHOW VARIABLES LIKE 'time_zone';

See tagastab praeguse ajavööndi. Seadistust saab muuta järgmiselt:

SET time_zone = '+09:00';

TIMESTAMP vs DATETIME ajavöönditega

DATETIME ei arvesta ajavööndit, kuid TIMESTAMP teisendatakse alati UTC-sse. Seetõttu sobib TIMESTAMP mitme ajavööndi süsteemides paremini.

4. 2038. aasta probleem ja selle mõju

2038. aasta probleem tuleneb 32-bitise TIMESTAMP piirangust. Kuna väärtus põhineb sekunditel alates 1. jaanuarist 1970, tekib ületäitumine 19. jaanuaril 2038 kell 03:14:07 UTC.

Kuidas 2038. aasta probleemi vältida

Lahendus on kasutada 64-bitiseid süsteeme või laiema vahemikuga DATETIME tüüpi. DATETIME katab vahemiku aastatest 1000 kuni 9999, mistõttu see ei kannata 2038. aasta piirangutest.

Samuti aitab süsteemide uuendamine 64-bitisele platvormile vältida probleemi täielikult.

5. TIMESTAMP-i praktilised näited

TIMESTAMP-i saab kasutada mitte ainult aja salvestamiseks, vaid ka automaatseks uuendamiseks ja mitmesugusteks logimisfunktsioonideks.

Automaatne hetkeaja sisestamine

Kui määrata vaikeväärtuseks CURRENT_TIMESTAMP, sisestatakse iga uue rea loomisel automaatselt praegune aeg.

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

Automaatne uuendamisaja salvestamine

Määrates ON UPDATE CURRENT_TIMESTAMP, logitakse iga rea uuendamise aeg automaatselt.

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

Mitu TIMESTAMP veergu

Tabelis võib olla mitu TIMESTAMP veergu, kuid vaikeväärtusena saab määrata CURRENT_TIMESTAMP ainult ühele. Teised veerud vajavad kas käsitsi väärtust või DATETIME-i kasutamist.

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. TIMESTAMP-i kasutamise tähelepanekud

TIMESTAMP-iga töötamisel tuleb arvestada piirangutega, et vältida andmete vastuolusid.

NULL ja vaikeväärtused

Vaikimisi on TIMESTAMP veerg NOT NULL. Kui soovid lubada NULL väärtusi, tuleb määrata DEFAULT NULL.

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

Võib kasutada ka DEFAULT 0, mis sisestab 0000-00-00 00:00:00, kuid see pole soovitatav, kuna rangetes SQL režiimides tekitab see vigu.

0000-00-00 00:00:00 probleem

Mõned versioonid lubavad seda väärtust, kuid tootmiskeskkonnas tuleks seda vältida. Selle asemel kasuta NULL või sobivat vaikeväärtust.

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

Süsteemi ajavööndi mõju

Kuna TIMESTAMP salvestatakse UTC-s, võib serveri ajavööndi muutmine muuta kuvatavat aega. Seetõttu on oluline ajavööndeid korrektselt hallata.

SET time_zone = 'Asia/Tokyo';

7. Kokkuvõte ja soovitused

TIMESTAMP on võimas tööriist MySQL-is kuupäevade ja kellaaegade haldamiseks. See sobib eriti hästi ajavöönditega arvestavates süsteemides ja automaatseks logimiseks. Kuid tuleb arvestada piirangutega, näiteks 2038. aasta probleem ja NULL väärtuste käsitlus.

Soovitused TIMESTAMP-i kasutamiseks

• Automaatse uuendamise vajaduse korral kasuta TIMESTAMP-i.
• Ajavöönditega süsteemides on TIMESTAMP sobiv tänu UTC-põhisele teisendusele.
• Pikemaajalise andmete haldamise jaoks (2038+ aasta) on parem kasutada DATETIME-i.

Kokkuvõttes tuleks valida TIMESTAMP või DATETIME vastavalt süsteemi nõuetele.

8. Korduma kippuvad küsimused (FAQ)

Siin on toodud sagedased küsimused, mis aitavad lahendada TIMESTAMP-iga seotud probleeme.

Millal kasutada TIMESTAMP-i ja millal DATETIME-i?

TIMESTAMP sobib mitme ajavööndiga süsteemidesse ja automaatseks logimiseks. DATETIME sobib siis, kui on vaja ajavööndist sõltumatut, stabiilset aega.

Kas TIMESTAMP ei tööta pärast 2038. aastat?

Jah, 32-bitine TIMESTAMP ei toeta kuupäevi pärast 19. jaanuari 2038. Lahendus on kasutada 64-bitist süsteemi või DATETIME tüüpi.

Kuidas lubada TIMESTAMP-is NULL väärtusi?

Peab määrama DEFAULT NULL.

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

Kas ajavööndi muutmine mõjutab olemasolevaid TIMESTAMP andmeid?

Andmed salvestatakse UTC-s, kuid kuvatakse uue ajavööndi põhjal. Seega muutub kuvatav aeg, kuigi andmed ise jäävad samaks.

Kas CURRENT_TIMESTAMP takistab kindla aja sisestamist?

CURRENT_TIMESTAMP lisab praeguse aja, kuid vajadusel saab käsitsi sisestada kindla kuupäeva ja kellaaja.

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