Dzisiejszy wpis ponownie będzie krótki i prosty, ponownie też podejmę temat zapytań SQL przydatnych w codziennej pracy programisty. Czym w takim razie się zajmiemy? Otóż, w związku z tym, że „czasem” musimy wykonać pewne instrukcje w zależności od istnienia bądź nieistnienia rekordów dodanych w konkretnym przedziale czasu (np. wczoraj, w ostatnim tygodniu), w tym wpisie chciałbym pokazać, jak to zrobić w prosty i przyjemny sposób. Zapraszam do lektury.
Fotografia: thejaymo, CC-BY-SA.
SQL: Znajdowanie rekordów dodanych w przedziale czasu.
Na początku załóżmy, że będziemy operować na następującej tabeli z danymi:
CREATE TABLE IF NOT EXISTS `posts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `content` text DEFAULT NULL, `createdAt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Krótko: mamy tabelę zawierającą wpisy [np. na blogu] – każdy z nich składa się z tytułu, treści i daty dodania.
Aby otrzymać wyniki pochodzące z tej tabeli musimy wykonać następujące zapytanie:
SELECT `id`, `name`, `content`, `createdAt` FROM posts
Nie było to zbyt trudne, prawda? Nasze zapytanie ma jednak istotny problem – zwróci baaardzo dużo danych, a my, zgodnie z tytułem wpisu chcemy dostać tylko te, które zostały opublikowane w pewnym odstępie czasu.
Jak to osiągnąć? Spróbujmy z przykładem – weźmy wszystkie wpisy opublikowane przedwczoraj. Przedwczoraj, to znaczy nie wcześniej niż dwa dni temu i nie później niż wczoraj o północy. Odpowiednie zapytanie SQL przedstawia się następująco:
SELECT `id`, `name`, `content`, `createdAt` FROM posts WHERE createdAt > DATE(DATE_SUB(NOW(), INTERVAL 2 DAY)) AND createdAt < DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
Nie wygląda na specjalnie skomplikowane, prawda? Zobaczmy więc, co oznaczają poszczególne elementy klauzuli WHERE.
Główny warunek „createdAt > X AND createdAt < Y” jest jasny, także mogę go pominąć w opisie. Cała zabawa zaczyna się w momencie, kiedy wgłębimy się w istotę funkcji opisujących punkty czasu.
Pierwsza funkcja otaczająca oba wyrażenia to DATE() – zwraca ona samą datę z podanego argumentu. Oznacza to w uproszczeniu tyle, że jeśli przekażemy do niej wartość pola typu DATETIME, to dostaniemy samą datę. Jeśli chcielibyśmy, żeby to zapytanie zwróciło nie rekordy opublikowane przedwczoraj, ale np. 24h temu poczynając od teraz, wystarczy usunąć tą funkcję i zostawić same jej argumenty.
Rdzeniem całego zapytania jest funkcja DATE_SUB(), która najprościej mówiąc odejmuje jedną datę od drugiej i zwraca odpowiedni wynik. Klucz do sukcesu to możliwość wstawienia do jej argumentów operatora INTERVAL, który potrafi „wygenerować” dowolny odstęp w czasie. W związku z tym, jeśli odejmiemy od wartości funkcji NOW() odstęp INTERVAL 1 DAY, to otrzymamy dokładny punkt w czasie… 24 godziny temu. Podobne zadanie realizuje funkcja DATE_ADD(), która oczywiście zamiast odejmować, dodaje podane daty i zwraca odpowiedni wynik. Możemy jej użyć, żeby sprawdzić np. daty i czas w przyszłości. ;]
Oczywiście DAY nie jest jedyną wartością, jaką może przyjąć wspomniany operator. Do wyboru mamy:
- SECOND
- MINUTE
- HOUR
- DAY
- MONTH
- YEAR
oraz wiele więcej. Wystarczy skorzystać z konstrukcji takich, jak np.:
- INTERVAL 2 YEAR
- INTERVAL 4 WEEK
- INTERVAL 2 SECOND
a życie od razu stanie się prostsze.
Mam nadzieję, że ten wpis przyda Wam się w rozwiązywaniu nietrywialnego problemu, jakim jest operowanie na datach i czasie w programowaniu. Miłego programowania. ;]
Warto przeczytać.
Trwa ładowanie…
Uzupełnię wpis o linka dla fanów SQL Servera: http://msdn.microsoft.com/en-us/library/ms186819.aspx
and more: http://msdn.microsoft.com/en-us/library/ms186724.aspx
Ech, a MS SQL jak zwykle musi robić po swojemu. ;] Oczywiście żart – dzięki za linki, Usher!
SQL: Znajdowanie rekordów w przedziale czasu.
Kilka razy przekonaliśmy się, że wrzucanie NOW() do WHERE’a potrafi się źle skończyć. Takie zapytania przez MySQLa nie są keszowane. Co prawda przy mały ruchu nie robi to wrażenia, lecz w sytuacji gdy ruch jest naprawdę spory, a zapytanie skomplikowane to brak tego kesza może poważnie odbić się na wydajności.
Oczywiście pomijam tutaj fakt, że takie rzeczy powinny być dodatkowo keszowane z poziomu aplikacji ;)
BTW – po tytule notki myślałem, że przedstawisz sposób aby zapytanie wykonywało się tylko przez określony czas, a potem przerywało swoje działanie ;)
PHP 5.4 Alpha
W moim przypadku cache jest właśnie na poziomie aplikacji, także nie muszę się aż tak martwić o wydajność bazy danych. Zapytanie, z którego korzystałem w projekcie nie jest skomplikowane – to zwykły SELECT z kilkoma warunkami połączonymi AND.
Skoro jednak mówisz, że nie będzie to działało zbyt dobrze, to co proponujesz w zamian? Przekazywanie daty z poziomu PHP bezpośrednio do zapytania? Przecież w jakiś sposób musimy osiągnąć tego typu rzeczy.
Co do „BTW” – nie słyszałem, żeby coś takiego było możliwe, ale pomysł wydaje się ciekawy, więc poszukam czegoś na ten temat. Może akurat znajdzie się jakieś rozwiązanie? ;]
SQL: Znajdowanie rekordów w przedziale czasu.
A czy nie szybciej jest przechowywać czas jako INT (czas UNIXA) i wykonywać zwykłe WHERE zamiast wywoływać dodatkowe funkcje MySQL?
Zamiast NOW() można wstawiać datę wygenerowaną przez aplikację. Nasz „Now()” był po prostu aktualną datą dokładną co do minuty (czyli MySQL keszował wewnętrznie zapytanie na ok minutę).
Problem pojawił się kiedy wyniki zapytania wrzucaliśmy do naszego kesza. ID kesza było md5 zapytania, a to się co minuta zmieniało i pliki zaczęły w szalonym tempie przyrastać ;)
PHP 5.4 Alpha
@Jacek Smolak: A jak wtedy chcesz operować na tej dacie? Licząc sekundy, które trzeba odjąć / dodać? To może nie być zbyt czytelne od strony aplikacji. Zgadzam się jednak, że na pewno byłoby to bardziej wydajne, chociaż ciekaw jestem o ile bardziej, niż to, co zaproponowałem we wpisie. Jakiś mały benchmark? ;]
SQL: Znajdowanie rekordów w przedziale czasu.
@Radosław Mejer: Aha, czyli tak jak przewidywałem. ;] Poza tym, jak wrzuca się pliki do cache, to jednak wypada pomyśleć nad mechanizmem automatycznej rewalidacji, tj. zarówno tworzenia nowych plików jak i kasowania starych. Ja np. zazwyczaj grupuję odpowiednie pliki cache w katalogach i w momencie, kiedy jeden plik z danego zbioru jest przeładowywany, to w domyśle wszystkie dane z tego katalogu są już „niepewne”, a więc kasowane.
SQL: Znajdowanie rekordów w przedziale czasu.
I właśnie dlatego wszelkie cudowania z now() i podobnymi funkcjami po stronie bazy są złe. Z tego też powodu robi się nieco inaczej pod kątem optymalizacji. Zapytanie składa się już po stronie języka korzystając z jego funkcji obróbki czasu i daty. Dzięki temu do bazy trafia już pełne zapytanie, którego baza nie musi sama przeliczać, a tym samym może sobie je cache’ować wewnętrznie na bardzo długo (zależnie od samego zapytania) bez korzystania z mechanizmu cache’u aplikacji.
Cóż, chciałem pokazać, jak to bezboleśnie zrobić po stronie SQLa, ale skoro wszyscy tutaj tak narzekają na optymalizację, to chyba zostaje jednak pójść za radą @Jacka Smolaka i samemu przeliczać INTy. ;]
SQL: Znajdowanie rekordów w przedziale czasu.
Uch… nie chodziło mi o taką reakcję :) W każdym razie, możesz na intach też działać, dodaj do rachunku funkcję FROM_UNIXTIME i inty nie będą straszne. Bo faktycznie, czasami trzeba coś na surowej bazie zrobić – wówczas ten poradnik jest jak znalazł.
I o to chodzi, a nie, żeby się od razu spierać o optymalność danego rozwiązania. Pierwsze pozytywne słowa na temat tego wpisu – dzięki Jacku!
SQL: Znajdowanie rekordów w przedziale czasu.
hmmm…
[..] WHERE createdAt BETWEEN fromDate AND toDate
?
(pod fromDate i toDate podstawić preferowany sposób daty)
Też można. ;] Ale to tylko zmiana zapisu [lukier składniowy - ja i tak piszę "normalne" warunki], dalej cała magia polega jednak na zawartości fromDate i toDate.
SQL: Znajdowanie rekordów w przedziale czasu.
Nikt nie zamierzał Cię tu bić na blogu :D Ja napisałem jedynie, że jeśli komuś zależy na optymalizacji nie powinno się tego stosować z racji tego, iż używanie now() nie zapewni cache’owania w bazie. Tak więc jeśli to częsta operacja, powinno się jej nie używać lub zapewnić inną formę cache’owania.
@thek
Jeśli chodzi o optymalizacje to dodam również, że gdy zawieramy jakieś funkcje w sekcji WHERE to automatycznie tracimy możliwość użycia indeksów przez baze, mimo że funkcja operuje na kolumnie na której założony jest indeks.
Fakt przy użyciu zapytania który wybiera nam jakiś przedział danych użycie indeksu nie daje nam takiego dużego wzrostu jak przy pobieraniu jednego wiersza, ale mimo wszystko wzrost jest :)
Ot taka ciekawostka.
Dzięki za litość. ;] A informacje o optymalizacji są istotne, także wcale nie uważam Waszych komentarzy za jakiś bezcelowy atak na mnie. Warto mieć to w pamięci, bo faktycznie, na blogu nie dam rady przewidzieć wszystkich sytuacji brzegowych, a to jest najbardziej istotne w programowaniu.
SQL: Znajdowanie rekordów w przedziale czasu.
@majne: Ciekawe, dobrze wiedzieć. Bazy danych to kolejny obszar wiedzy, gdzie zawsze jest jakiś nowy szczegół do ogarnięcia, także z przyjemnością czytam tego typu nowinki, bo sam siedzę głównie w „szczegółach” PHP. ;]
SQL: Znajdowanie rekordów w przedziale czasu.
Pamiętaj: używaj tych rozwiązań do małych tabel.
Rozwiązania ciekawe, ale bardzo mało wydajne.
PHPCon Poland 2011 – zapisy start!
Dzięki za radę, aczkolwiek w rozwiązaniach, gdzie liczy się wydajność, a nie sam efekt pewnie zanim zastosowałbym cokolwiek, poprzedziłbym to odpowiednim profilowaniem.
SQL: Znajdowanie rekordów w przedziale czasu.
tu nie chodzi o profilowanie, tylko wiedza co jest słoniem ;) Jak wiemy, wszystko zależy od kontekstu.
PHPCon Poland 2011 – zapisy start!
Pingback: symfony: Przekazywanie dodatkowych danych do klasy formularza. « Tomasz Kowalczyk