Funkcje przetwarzania daty i czasu w MySQL

Opublikowany przez

MySQL posiada wbudowane funkcje, dzięki którym w bardzo łatwy i przejrzysty sposób można operować na dacie i czasie.

Thank you for reading this post, don't forget to subscribe!

Listę wszystkich możliwości z datą i czasem odnajdziecie na oficjalnej stronie MySQL : Date and Time Functions

Poniżej postaram się przedstawić kilka z nich w raz z przykładami użycia:

CURDATE() i CURRENT_DATE – zajmują się pobieraniem aktualnej daty (z serwera) w formacie rrrr-mm-dd

 SELECT CURDATE();
/* przykładowym wynikiem będzie 2012-05-25 */

CURTIME() i CURRENT_TIME – zajmują się pobieraniem aktualnego czasu (z serwera) w formacie hh:mm:ss

 SELECT CURTIME();
/* przykładowym wynikiem będzie 10:02:10 */

SYSDATE(), CURRENT_TIMESTAMP i NOW() – zajmują się pobieraniem i aktualnej daty, i aktualnego czasu w formacie rrrr-mm-dd gg:mm:ss

 SELECT NOW();
/* przykładowym wynikiem będzie 2012-05-25 10:02:10 */

DATE_ADD() , DATE_SUB() – zajmują się odpowiednio dodawaniem i odejmowaniem od podanej daty określonej liczby jednostek.

/* składnia wygląda następująco:
   DATE_SUB(data, INTERVAL jednostki) */

 SELECT DATE_SUB (NOW(), INTERVAL 1 YEAR)

/* przykładowym wynikiem będzie 2011-05-25 10:02:10 */

Formatowanie daty w MySQL

Funkcja DATE_FORMAT() – przedstawia datę zgodnie ze zdefiniowanym formatem. Jej składnia wygląda następująco: DATE_FORMAT(data,format).

SELECT DATE_FORMAT(NOW(),'%d %M %Y');
/* przykładowym wynikiem będzie 25 May 2012*/

Poniżej zamieszczam pełną listę znaczników stosowanych do formatowania daty i czasu:

  • %a – zwraca skróconą nazwę dnia tygodnia (Sun..Sat)
  • %b – zwraca skróconą nazwę miesiąca (Jan..Dec)
  • %c – zwraca numer miesiąca (0..12)
  • %D – zwraca dzień miesiąca wraz z liczebnikiem porządkowym (0th, 1st, 2nd, 3rd, …)
  • %d – zwraca dzień miesiąca w formacie dwucyfrowym (00..31)
  • %e – zwraca dzień miesiąca (0..31)
  • %f – zwraca liczbę mikrosekund (000000..999999)
  • %H – zwraca godzinę w formacie 24 godzinnym, dwucyfrowym (00..23)
  • %h – zwraca godzinę w formacie 12 godzinnym, dwucyfrowym (01..12)
  • %I – zwraca godzinę w formacie 12 godzinnym, dwucyfrowym (01..12)
  • %i – zwraca liczbę minut (00..59)
  • %j – zwraca numer dnia w roku w formacie trzycyfrowym (001..366)
  • %k – zwraca godzinę w formacie 24 godzinnym (0..23)
  • %l – zwraca godzinę w formacie 12 godzinnym (1..12)
  • %M – zwraca pełną nazwę miesiąca (January..December)
  • %m – zwraca numer miesiąca (00..12)
  • %p – zwraca AM, lub PM w zależności od pory dani
  • %r – zwraca czas w formacie 12 godzinnym po któym występuje informacja AM/PM
  • %S – zwraca liczbę sekund (00..59)
  • %s – zwraca liczbę sekund (00..59)
  • %T – zwraca czas w formacie 24 godzinnym (hh:mm:ss)
  • %U – zwraca numer tygodnia (00..53) (niedziela jest dniem pierwszym, liczone od 00)
  • %u – zwraca numer tygodnia (00..53) (poniedziałek jest dniem pierwszym, liczone od 00)
  • %V – zwraca numer tygodnia (01..53) (niedziela jest pierwszym dniem, liczone od 01)
  • %v – zwraca numer tygodnia (01..53) (poniedziałek jest pierwszym dniem, liczone od 01)
  • %W – zwraca pełną nazwę dnia tygodnia (Sunday..Saturday)
  • %w – zwraca dzień tygodnia liczbowo (0=Niedziela..6=Sobota)
  • %X – zwraca rok danego tygodnia (niedziela jest pierwszym dniem)
  • %x – zwraca rok danego tygodnia (poniedziałek jest pierwszym dniem)
  • %Y – zwraca rok w formacie czterocyfrowym
  • %y – zwraca rok w formacie dwucyfrowym
  • %% – zwraca znak %

Lista funkcji części składowych daty:

  • DAYNAME(data) – zwraca pełną nazwę dnia tygodnia
  • MONTHNAME(data) – zwraca pełną nazwę miesiąca
  • DAYOFWEEK(data) – zwraca dzień tygodnia w postaci cyfry
  • WEEKDAY(data) – zwraca dzień tygodnia w postaci cyfry (0-53) (0 – Poniedziałek)
  • DAYOFMONTH(data) – zwraca dzień miesiąca w postaci cyfry
  • DAYOFYEAR(data) – zwraca dzień w roku w postaci cyfry (1-366)
  • QUARTER(data) – zwraca numer kwartału
  • WEEK(data) – zwraca numer tygodnia w roku
  • SECOND(data) – zwraca liczbę sekund
  • MINUTE(data) – zwraca liczbę minut
  • HOUR(data) – zwraca godzinę
  • MONTH(data) – zwraca numer miesiąca w roku
  • YEAR(data) – zwraca rok
  • TO_SECONDS(data) – zwraca liczbę sekund, które minęły od roku 0
  • TO_DAYS(data) – zwraca liczbę dni, które minęły od roku 0
  • LAST_DAY(data) – zwraca ostatni dzień miesiąca dla daty
  • FROM_DAYS(dni) – zwraca datę, którą wskazuje liczba dni od początku naszej ery
 SELECT YEAR(NOW());
/* przykładowym wynikiem będzie 2012 */

Przykłady zastosowania :

Załóżmy, iż posiadamy tabele test: z kolumnami id, dane i data (gdzie id – ustawiona jest na AUTO_INCREMENT)
Dodawanie nowych danych z aktualna datą:

INSERT INTO test (dane, data) values ('nowe dane',NOW());

Dodawanie nowych danych z datą wczorajszą:

INSERT INTO test (ip,data) values ('nowe dane',DATE_SUB(NOW(),INTERVAL 1 DAY));

Wyszukiwanie danych starszych niż pięć miesięcy:

SELECT * FROM test WHERE data < DATE_SUB (NOW(), INTERVAL 5 MONTH);

Usuwanie danych starszych niż 12 godzin:

DELETE FROM test WHERE data < DATE_SUB(NOW(),INTERVAL 12 hour);

Zwiększanie daty o 1 rok:

UPDATE test SET data = DATE_ADD(data, INTERVAL 1 YEAR) WHERE id='1';

Z czasem będę starał się dodawać następne przykłady. W powyższym poście oczywiście nie ująłem wszystkiego ze względu na zbyt obszerny temat, ale mam nadzieje, że choć trochę przybliżyłem wam tajniki magicznych funkcji daty i czasu w MySQL.