Jak efektywnie pracować z datami i godzinami w MySQL

Jak efektywnie pracować z datami i godzinami w MySQL

Daty i godziny są ważne, pomagają utrzymać porządek i są integralnym aspektem każdej operacji oprogramowania.





Wydajna praca z nimi w bazie danych może czasami wydawać się myląca, niezależnie od tego, czy chodzi o różne strefy czasowe, dodawanie/odejmowanie dat i inne operacje.



Poznaj różne dostępne funkcje MySQL, aby łatwo obsługiwać i zarządzać datami/godzinami w Twojej bazie danych.





Praca ze strefami czasowymi

Aby zachować standaryzację, powinieneś pracować tylko z datami / godzinami w strefie czasowej UTC. Za każdym razem, gdy nawiązujesz połączenie z bazą danych MySQL, powinieneś przełączyć strefę czasową na UTC, co można zrobić za pomocą następującej instrukcji SQL:

SET TIME_ZONE = '+0:00'

Ponieważ wszystkie daty będą teraz zapisywane w UTC, zawsze wiesz, z czym pracujesz, dzięki czemu wszystko jest bardziej uproszczone i proste.



co zrobić, jeśli konto na Facebooku jest sklonowane?

W razie potrzeby możesz łatwo przekształć strefę czasową o dowolnej wartości daty i godziny / znacznika czasu z poręcznym KONWERSJA_TZ() Funkcja MySQL. Najpierw musisz znać przesunięcie, na przykład PST na zachodnim wybrzeżu Ameryki Północnej to UTC -08:00, więc możesz użyć:

SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

To skutkuje 2021-02-04 13:47:23 co jest dokładnie poprawne. Trzy argumenty przekazane do KONWERSJA_TZ() to pierwsza data i godzina / znacznik czasu, od której zaczynasz (użyj now() dla bieżącego czasu), druga zawsze będzie „+0: 00” ponieważ wszystkie daty w bazie danych są wymuszane do czasu UTC, a ostatnia to przesunięcie, na które chcemy przekonwertować datę.



Dodaj / Odejmij daty

Wiele razy trzeba dodawać lub odejmować od dat, na przykład, jeśli trzeba pobrać rekordy sprzed tygodnia lub zaplanować coś na miesiąc od teraz.

Na szczęście MySQL ma doskonałe DATE_ADD() oraz DATE_SUB () funkcje czyniące to zadanie niezwykle łatwym. Na przykład możesz odjąć dwa tygodnie od bieżącej daty za pomocą instrukcji SQL:



SELECT DATE_SUB(now(), interval 2 week);

Jeśli zamiast tego chcesz dodać trzy dni do istniejącego znacznika czasu, użyjesz:

SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Obie funkcje działają tak samo, pierwszym argumentem jest znacznik czasu, od którego zaczynasz, a drugim interwał dodawania lub odejmowania. Drugi argument jest zawsze sformatowany tak samo, zaczynając od słowa interwał po której następuje wartość liczbowa i sam interwał, który może być dowolnym z następujących: sekunda, minuta, godzina, dzień, tydzień, miesiąc, kwartał, rok.

Na przykład, jeśli chcesz pobrać wszystkie logowania, które miały miejsce w ciągu ostatnich 34 minut, możesz użyć instrukcji SQL, takiej jak:

SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Jak widać, spowodowałoby to pobranie wszystkich rekordów z loginy tabela z datą logowania większą niż aktualny czas minus 45 minut, czyli innymi słowy ostatnie 45 minut.

Uzyskaj różnicę między datami

Czasami trzeba sprawdzić, ile czasu upłynęło między dwiema datami. Możesz łatwo uzyskać liczbę dni między dwiema różnymi datami za pomocą DATA RÓŻNICA funkcja, taka jak poniższa instrukcja SQL:

SELECT DATEDIFF(now(), '2020-12-15');

ten DATA RÓŻNICA funkcja przyjmuje dwa argumenty, z których oba są znacznikami daty/czasu i podaje liczbę dni między nimi. Powyższy przykład pokaże liczbę dni, które upłynęły od 15 grudnia 2020 r. do dziś.

Aby uzyskać liczbę sekund między dwiema datami, TO_SECONDS() funkcja może się przydać, na przykład:

SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Spowoduje to liczbę sekund między dwiema podanymi datami.

Wyodrębnij segmenty z dat

Istnieją różne funkcje MySQL, które pozwalają łatwo wyodrębnić określone segmenty z dat, na przykład jeśli chcesz tylko miesiąc, dzień roku lub godzinę. Oto kilka przykładów takich funkcji:

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

Powyższe instrukcje SQL skutkowałyby 02 , bieżąca godzina, oraz 196 ponieważ 15 września jest 196 dniem roku. Oto lista wszystkich dostępnych funkcji wyodrębniania dat, z których każda przyjmuje tylko jeden argument, z którego pobierana jest data:

- SECOND()
- MINUTE()
- HOUR()
- DAY()
- WEEK() - Number 0 - 52 defining the week within the year.
- MONTH()
- QUARTER() - Number 1 - 4 defining the quarter of the year.
- YEAR()
- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
- LAST_DAY() - The last day in the given month.
- DATE() - The date in YYYY-MM-DD format without the time.
- TIME() The time in HH:II:SS format without the date.
- TO_DAYS() - The number of days since A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 0.
- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Na przykład, jeśli chcesz pobrać tylko miesiąc i rok, w którym utworzono wszystkich użytkowników, możesz użyć instrukcji SQL, takiej jak:

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Spowoduje to odzyskanie wszystkich rekordów w obrębie użytkownicy tabeli i pokaż id#, miesiąc i rok, w którym każdy użytkownik został utworzony.

Grupowanie rekordów według daty i okresu

Jednym z doskonałych zastosowań funkcji daty jest możliwość grupowania rekordów według okresu za pomocą GRUPUJ WEDŁUG w instrukcjach SQL. Na przykład, może chcesz pobrać łączną kwotę wszystkich zamówień w 2020 roku pogrupowanych według miesięcy. Możesz użyć instrukcji SQL, takiej jak:

SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Spowoduje to pobranie wszystkich zamówień złożonych w roku 2020, pogrupowanie ich według miesiąca, w którym zostały utworzone, i zwrócenie 12 rekordów pokazujących łączną kwotę zamówienia w każdym miesiącu roku.

Pamiętaj, że w celu uzyskania lepszej wydajności indeksowania zawsze najlepiej jest unikać używania funkcji daty, takich jak ROK() wewnątrz klauzuli WHERE instrukcji SQL, a zamiast tego użyj POMIĘDZY operator, jak pokazano w powyższym przykładzie.

Nigdy więcej nie myl z datami

Korzystając z powyższej wiedzy, możesz teraz efektywnie pracować, tłumaczyć i wykonywać operacje na datach i godzinach w szerokim zakresie przypadków użycia.

Pamiętaj, aby zawsze używać czasu UTC podczas pracy z datami dla uproszczenia i korzystaj z powyższych wskazówek, aby efektywnie zarządzać datami w oprogramowaniu, niezależnie od tego, czy chodzi o wykonywanie prostych obliczeń, czy łatwe pobieranie raportów pogrupowanych według okresów dat.

Jeśli jesteś trochę nowy w SQL, koniecznie sprawdź te podstawowe polecenia SQL aby pomóc ulepszyć korzystanie z SQL.

Udział Udział Ćwierkać E-mail Jak tworzyć profesjonalne raporty i dokumenty w programie Microsoft Word

Ten przewodnik analizuje elementy profesjonalnego raportu i przegląda strukturę, styl i finalizację dokumentu w programie Microsoft Word.

Czytaj dalej Powiązane tematy O autorze Matt Dizak(18 opublikowanych artykułów) Więcej od Matta Dizaka

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować