Kurs MySQL – data i czas cz. 1 – Magento Developer po godzinach

Poznaliśmy już relację i różne typy danych, czas by pobawić się czasem. Podczas tej lekcji przedstawię m.in. przydatne kwerendy pozwalające zawęzić wynik zapytania. Dowiesz się również jak automatycznie uzupełniać wiersze w tabeli o aktualny UNIX Timestamp.

Formaty daty i czasu – ISO 8601:2004

Większość sfer naszego życia opisują standardy i tak też jest w przypadku czasu. Obecnie obowiązującym standardem jest ISO 8601:2004 – trzecia wersja dokumentu datowana na 2004-12-03. Polski odpowiednik to norma PN-EN 28601:2002.

Nim przejdziemy do pisania kwerend warto wiedzieć, że przechowywanie wartości opisujących go nie jest kwestią przypadku. Najprostszą definicją tej koncepcji jest zasada od ogółu do szczegółu, czyli daty najistotniejszej/najogólniejszej (roku) po cząstkę daty najbardziej szczegółową ale bez innych nic nie znaczącą – sekundę.

Formaty dat

Norma zakłada trzy rodzaje zapisu dat:

daty kalendarzowe (format – YYYY-MM-DD, dopuszcza się opuszczenie separatorów) – to data, w której dzień liczony kolejno w każdym kolejnym miesiącu i tak np 1 styczeń 2009 zapiszemy jako 2009-01-01 albo 20090101.
daty porządkowe (format – YYYY-DDD, dopuszcza się opuszczenie separatorów) – to data, w której dzień przedstawiony jest jako kolejny w całym roku i tak np 1 stycznia 2009 zapiszemy jako 2009-001, a w formacie podstawowym 2009001
daty tygodniowe (format – YYYY-Www-D, dopuszcza się opuszczenie separatorów), to taka w której dzień jest oznaczany jako kolejny w kolejnym tygodniu roku i tak np. 1 stycznia 2009 zapiszemy jako 2009-W01-1, a w formacie podstawowym 2009W011

Warto dodać, że standard ten korzysta z kalendarza gregoriańskiego. Rok 0001 oznacza pierwszy rok naszej ery, poprzedni to 0000, a kolejne otrzymują znak -. I tak np 2 rpne. będzie posiadał zapis -0001.

Format czasu

Standard używa zakresu 24-godzinnego (format hh:mm:ss, bądź bez separatorów hhmmss). Północ zapisywana jest zarówno jako 00:00 jak i 24:00 tj. 24:00 31 grudnia 2019 jest tym samym momentem co 00:00 01 stycznia 2020 r. Do tego wszystkiego dochodzą jeszcze oczywiście strefy czasowe. Oznaczenia czasu UTC dokonuje się przez dodanie litery Z np 10:55 będzie miała zapis 10:55Z.

Łączny zapis daty i czasu

Aby połączyć datę i czas zgodnie ze standardem ISO 8601 należy użyć łącznika T i tak np. 23:30:00 23 listopada 2019 będzie miało format 2019-11-2T23:30:00Z.

Formaty daty i czasu a typy danych w MySQL

W przypadku rekordów w bazie danych formaty wymuszane są przez typy danych

YYYY-MM-DD hh:mm:ss – DATETIME
od 1000-01-01 00:00:00 do 9999-12-31 23:59:59
YYYY-MM-DD – DATE
od 1000-01-01 do 9999-12-31
TIMESTAMP – czyli data i czas liczony od początku epoki systemu UNIX, 1970-01-01 00:00:00, do momentu kiedy 32-bitowe pole przechowujące liczbę sekund, jaka upłynęła od tej daty ulegnie przepełnieniu w roku 2037.
hh:mm:ss – TIME
Czas mieszczący się w przedziale od -838:59:59 do 838:59:59
YEAR – czyli rok.
Dozwolone wartości to przedział od 1901 do 2155 oraz wartość 0000. MySQL wyświetla wartość typu YEAR w formacie YYYY

Wyniki dodane wcześniej/później niż…

Operując na danych, często będziesz potrzebował zwrócić rekordy dodane/zmodyfikowane w określonym przedziale czasu. Aby móc przećwiczyć dotychczas zdobytą wiedzę przygotuj stwórz poniższe tabele i zasil je danymi:

CREATE TABLE `events_datetime` (
  `idevents` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NULL,
  `event_time` DATETIME NOT NULL,
  PRIMARY KEY (`idevents`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Koncert ', '2020-01-13 22:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Trening', '2020-01-12 12:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Wolontariat', '2020-01-13 12:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Kino', '2020-01-12 16:30:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Teatr', '2020-01-13 15:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Basen', '2020-01-11 08:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Mecz siatkówki w TV', '2020-01-13 00:00:00');
INSERT INTO `events_datetime` (`title`, `event_time`) VALUES ('Gala KSW', '2020-01-11 23:59:59');

Jeśli wszystko przebiegło poprawnie wynik zapytania:

SELECT * FROM `events_datetime`;

MySQL – rekordy dodane wcześniej niż…

Interesują nas wszystkie wydarzenia które odbędą się wcześniej niż 13 stycznia. Aby je zwrócić można skorzystać z jednego z 2 poniższych zapytań (w przypadku formatu skróconego, domyślnie czas liczony jest od północy):

SELECT * FROM `events_datetime` WHERE `event_time` < '2020-01-13 00:00:00';
SELECT * FROM `events_datetime` WHERE `event_time` < '2020-01-13';

Jeżeli oczekiwanym wynikiem jest przedział lewostronnie domknięty (tj. data graniczna będzie zawierać się w zbiorze wyników), należy dodatkowo użyć znaku równości.

SELECT * FROM `events_datetime` WHERE `event_time` <= '2020-01-13 00:00:00';
SELECT * FROM `events_datetime` WHERE `event_time` <= '2020-01-13';

Jeżeli naszym celem jest zwrócenie wszystkich wydarzeń, które już się odbyły, czyli ich data jest usytuowana w przeszłości możemy użyć funkcji NOW():

SELECT *, now() FROM `events_datetime` WHERE `event_time` < NOW();

Pamiętaj, ze kolumna now() zawiera datę, która odpowiada czasowi, w którym zostało wykonane zapytanie.

MySQL – rekordy dodane później niż…

Interesują nas wszystkie wydarzenia, które odbędą się później niż 11 stycznia. Aby je zwrócić można skorzystać z jednego z 2 poniższych zapytań (w przypadku formatu skróconego, domyślnie czas liczony jest od północy):

SELECT * FROM `events_datetime` WHERE `event_time` > '2020-01-11 23:59:59';
SELECT * FROM `events_datetime` WHERE `event_time` >= '2020-01-12';

Jeżeli oczekiwanym wynikiem jest przedział prawostronnie domknięty (tj. data graniczna będzie zawierać się w zbiorze wyników), należy dodatkowo użyć znaku równości, jak w powyższym przykładzie ze skróconym formatem daty .

SELECT * FROM `events_datetime` WHERE `event_time` >= '2020-01-11 23:59:59';

Jeżeli naszym celem jest zwrócenie wszystkich wydarzeń, które dopiero się odbędą, czyli ich data jest usytuowana w przyszłości możemy użyć funkcji NOW():

SELECT *, now() FROM `events_datetime` WHERE `event_time` > NOW();