Na pewno nie raz budując zapytania w MySQL zetknąłeś się z potrzebą wybierania określonej daty lub godziny, czy to w liście zwracanych pól czy też w klauzuli WHERE. Nie będę dziś opisywać szczegółowo każdej wbudowanej funkcji do operowania na dacie / godzinie. Choć kilka z nich omówimy, to w większości wykorzystamy je oraz inne funkcje do obliczeń, których nie można osiągnąć bezpośrednio z tej grupy funkcji.
Zaczniemy od najprostszych.
Data za 21 dni od określonej daty:
SELECT DATE_ADD('2017-10-19', INTERVAL 21 DAY)
Data miesiąc wstecz:
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
Użyliśmy tu funkcji CURDATE, która zwraca aktualną datę. Pamiętaj, że między interwałem 1 MONTH a 30 DAY, jest różnica (w dniach też może być!). Najlepszym przykładem jest dodawanie do ostatniego stycznia 30 dni - uzyskamy poczatkowe dni marca. W przypadku 1 miesiąca uzyskamy ostatni dzień lutego (nastąpi ogranicznenie daty do ostatniego możliwego dnia lutego).
Rekordy nowsze niż:
SELECT * FROM tabela WHERE data>=DATE_SUB(CURDATE(), INTERVAL x DAY)
Porównujemy tu pole data z tabeli z aktualną datą pomniejszoną o X dni (czyli przesuwamy ją w przeszłość). Uzyskamy w ten sposób rekordy nie starsze niż X dni. Jeśli dziś jest 2017-10-20, to będziemy brać pod uwagę daty większe lub równie niż 2017-10-10. W zależności od tego jak potraktujemy znaczenie "niż", możemy zmienić nierówność na ostrą (wtedy graniczny dzień nie będzie brany pod uwagę, w przykładzie 2017-10-10) lub zmniejszyć X o jeden.
Analogicznie postępujemy z rekordami starszymi niż:
SELECT * FROM tabela WHERE data<DATE_SUB(CURDATE(), INTERVAL x DAY)
Jeśli nasza graniczna nie jest datą zależną od dnia dzisiejszego, to nic nie stoi na przeszkodzie podstawić do warunku określoną datę bez używania DATE_ADD / DATE_SUB.
Ostatni dzień miesiąca:
SELECT LAST_DAY('2017-10-19');
Tu mamy wbudowaną funkcję. Argumentem może być oczywiście wartość funkcji CURDATE.
Pierwszy dzień miesiąca:
SELECT DATE_ADD(DATE_SUB(LAST_DAY('2017-10-19'), INTERVAL 1 MONTH), INTERVAL 1 DAY);
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');
W pierszym przykładzie znajdujemy ostatni dzień miesiąca na podstawie daty, odejmujemy miesiąc otrzymując ostatni dzień poprzedniego miesiąca. Kiedy dodamy jeden dzień, otrzymamy pierwszy dzień miesiąca zadanej daty.
W drugim przypadku formatujemy datę korzystając z maski %Y (rok), %m (miesiąc) oraz po prostu stałego stringu 01 (czyli pierwszy dzień miesiąca).
Korzystając z drugiego przykładu powyżej, możesz obliczyć na podstawie aktualnej daty wybrany dzień:
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-15');
Rozwiązanie może dawać błędne wyniki dla 28, 29, 30 lub 31 dnia miesiąca.
Zaokrąglenie godziny do danego interwału (tu 5 minut):
SELECT SEC_TO_TIME(CEIL(TIME_TO_SEC(NOW())/300)*300)
Aktualny czas dzielimy na 300 sekund (5*60). Zaokrągalmy w górę dzięki funkcji CEIL i mnożymy ponownie przez 300. Mając czas w sekundach zamieniamy je na format TIME.
Rozjaśnijmy ten przykład. Weźmy czas 00:04:00 (dla innych godzin jest analogicznie, ale tu uzyskamy mniejsze liczby, więc będzie łatwiej zrozumieć). Czas zamieniony na sekundy to 240. 240/300=0.8. CEIL(0.8)=1. 1*300=300=5 minut. Dla przedziału 00:00:01 do 00:05:00, otrzymamy 00:05:00, dla przedziału 00:05:01 do 00:10:00, otrzymamy 00:10:00, itd.
Jeśli chcemy zaokrąglić czas do danego przedziału, ale w dół, to skorzystamy z:
SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(NOW())/300)*300)
Funkcja FLOOR zaokrągli nam czas do najbliższej liczby całkowitej. W ten sposób przedział 00:00:00 do 00:04:59 będzie stanowił czas 00:00:00, 00:05:00 - 00:09:59 czas 00:05:00, itd.
Numer kwadransu:
SELECT ((CEIL(TIME_TO_SEC('21:39:14')/900)-1) MOD 4)+1
Zamieniamy czas na sekundy i dzielimy przez 900 (15*60). Zaokrąglamy do najbliższej, większej liczby całkowitej. Od północy otrzymujemy kolejno: 1, 2, 3, 4, 5, 6, itd. Ponieważ wartości większe od 4 chcemy liczyć od 1 (5 kwarta->1 kwarta, 6->2) musimy użyć funkcji MOD, która w tym przypadku daje resztę z dzielenia przez 4. Jeśli jednak od wyniku nie odejmiemy wcześniej 1, to dla 1-6 otrzymalibyśmy odpowiednio: 1, 2, 3, 0, 1, 2. odejmujemy więc najpierw 1, następnie obliczamy modulo i otrzymujemy: 0, 1, 2, 3, 0, 1. Dodajemy 1 i mamy numer kwarty.
Numer tygodnia dla danej daty, względem pierwszego dnia miesiąca:
SELECT CEIL(DAYOFMONTH('2017-10-07')/7)
SELECT CEIL(DATE_FORMAT('2017-10-15','%d')/7)
Dla podanej daty obliczamy numer dnia w miesiącu, dzielimy przez 7 (tydzień) i zaokrąglamy do bliższej, większej liczby całkowitej. Pierwszy tydzień ma wartość 1. W drugim przykładzie wynik będzie identyczny, ale numer dnia daje nam funkcja DATE_FORMAT (%d - dzień).
Poniedziałek tygodnia, w którym zawiera się podana data:
SELECT DATE_SUB('2017-10-18', INTERVAL WEEKDAY('2017-10-18') DAY)
Funkcja WEEKDAY zwraca numer dnia tygodnia według numeracji 0 - poniedziałek, 6 - niedziela. W podanym przykładzie zwróci 2. Odejmujemy więc od podanej daty 2 i otrzymujemy poniedziałek (środę i poniedziałek dzielą 2 dni).
Data rozpoczęcia następnego tygodnia:
SELECT DATE_SUB('2017-10-15', INTERVAL WEEKDAY('2017-10-15')-7 DAY)
Jest to modyfikacja poprzedniego zapytania, ale zamiast dodawać 7 dni poprzez DATE_ADD, po prostu odejmujemy 7 dni mniej.
Różnica tygodni dla podanej daty względem daty początkowej:
SELECT FLOOR(DATEDIFF('2017-10-25', DATE_SUB('2017-10-18', INTERVAL WEEKDAY('2017-10-18') DAY))/7)
Po kolei:
- znajdujemy poniedziałek (dla daty startowej 2017-10-18 będzie to 2017-10-16),
- znajdujemy różnicę dni (9) między poniedziałkiem a zadaną datą (DATEDIFF),
- dzielimy różnicę dni przez 7 (tydzień), otrzymując 1.285,
- zaokrąglamy do najbliższej, mniejszej wartości całkowitej (FLOOR) i uzyskujemy 1.
W podanym przykładzie uzyskaliśmy 1, co oznacza, że jesteśmy 1 tydzień od tygodnia daty startowej.
Powyższe zapytanie można zmodyfikować znajdując poniedziałki dla obydwu dat i dzieląc różnice przez 7:
SELECT DATEDIFF(DATE_SUB('2017-10-25', INTERVAL WEEKDAY('2017-10-25') DAY), DATE_SUB('2017-10-18', INTERVAL WEEKDAY('2017-10-18') DAY))/7
Jeśli chcemy znaleźć numer tygodnia względem daty początkowej, to do powyższego przykładu dodajemy 1 dla dni w tygodniu data startowej lub następnych:
SELECT FLOOR(DATEDIFF('2017-10-16', DATE_SUB('2017-10-18', INTERVAL WEEKDAY('2017-10-18') DAY))/7) + SIGN(SIGN(DATEDIFF('2017-10-16','2017-10-18'))+1)
Jeśli nie uwzględniamy sytuacji kiedy zadana data jest mniejsza od daty startowej, wtedy drugi człon sumy stanowi po prostu wartość 1 (bez funkcji SIGN).
Wszystkie czwartki w miesiącu:
SELECT dzien FROM(
SELECT DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'),INTERVAL a.a + (10 * b.a) DAY) AS dzien FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS b
) AS kalendarz
WHERE dzien<=LAST_DAY(CURDATE()) AND WEEKDAY(dzien)=3
Użyliśmy tu zapytania generującego kalendarz (więcej znajdziesz we wpisie: Marian, a czy ty pamiętasz o naszej rocznicy? Czyli jak wygenerować "kalendarz" w MySQL.). Kalendarz startuje od pierwszego dnia aktualnego miesiąca (wyliczanie pierwszego dnia miesiąca jest w przykładzie wyżej), a ograniczony jest do dnia ostatniego (LAST_DAY) oraz selekcją 3 dnia tygodnia (czyli czwartku - WEEKDAY).
Nieparzyste czwartki w miesiącu:
SELECT dzien FROM(
SELECT DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'),INTERVAL a.a + (10 * b.a) DAY) AS dzien FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS b
) AS kalendarz
WHERE dzien<=LAST_DAY(CURDATE()) AND WEEKDAY(dzien)=3 AND CEIL(DAYOFMONTH(dzien)/7) MOD 2=1
Cel osiągniemy dodając do wcześniejszego przykładu warunek na numer tygodnia w miesiącu. Dzieląc numer dnia miesiąca przez 7 i sprowadzając od najbliższej, większej liczby całkowitej otrzymamy liczby 1, 2, 3, 4, 5, odpowiadające tygodniom. Reszta z dzielenia przez dwa daje nieparzyste czwartki (w tygodniach 1, 3, 5) lub parzyste (w tygodniach 2, 4).
Możemy również połączyć ostatnie dwa człony w warunku w jeden:
SELECT ... WHERE ... AND (WEEKDAY(dzien)+7*(CEIL(DAYOFMONTH(dzien)/7)-1)) IN(3,17,31)
Obliczamy tu podobnie jak wyżej numer tygodnia, sprowadzamy go do wartości 0-4. Kiedy go pomnożymy przez 7 i dodamy numer dnia w tygodniu, otrzymamy dla wszystkich czwartków wartości: 3 (1 tydzień), 10 (drugi tydzień), itd. My wybieramy tylko te w nieparzystych tygodniach. Pamiętaj, że w przypadku takiego obliczenia numer dnia może przyjąć wartość nawet 34 (jeśli pierwszym dniem miesiąca będzie niedziela, a miesiąc będzie mieć 31 dni).
Chcesz być zawsze na czasie? Korzystaj z tego wpisu!
Przydatne linki:
Funkcje daty i czasu w MySQL
Jak wygenerować "kalendarz" w MySQL.