Jak porównać daty w polach daty i godziny w Postgresql?

188

Mam do czynienia z dziwnym scenariuszem podczas porównywania dat w postgresql (wersja 9.2.4 w systemie Windows). Mam kolumnę w tabeli, która mówi „update_date” z typem „timestamp without strefa czasowa”. Klient może wyszukiwać w tym polu tylko według daty (tj: 2013-05-03) lub daty z godziną (tj: 2013-05-03 12:20:00). Ta kolumna ma obecnie wartość znacznika czasu dla wszystkich wierszy i ma tę samą część daty (2013-05-03), ale różnicę w części czasu.

Porównując tę kolumnę, otrzymuję różne wyniki. Podobnie jak następujące:

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-03' -> No results

select * from table where update_date >= '2013-05-03' AND update_date < '2013-05-03' -> No results

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-04' -> results found

select * from table where update_date >= '2013-05-03' -> results found

Moje pytanie brzmi: w jaki sposób mogę umożliwić pierwsze zapytanie, aby uzyskać wyniki, mam na myśli, dlaczego trzecie zapytanie działa, ale nie pierwsze?

Czy ktoś może mi w tym pomóc? Z góry dziękuję.

— użytkownik 2866264
źródło

278

@Nicolai ma rację co do przesyłania i dlaczego warunek jest fałszywy dla jakichkolwiek danych. myślę, że wolisz pierwszą formę, ponieważ chcesz uniknąć manipulowania datą w ciągu wejściowym, prawda? nie musisz się bać:

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

— tylko ktoś
źródło

Czy ta składnia ( '2013-05-03'::datei '1 day'::interval) PostgreSQL jest specyficzna?

— Frozen Flame

5

@FrozenFlame tak to jest. standardowa składnia to CAST('2013-05-03' AS DATE) + CAST('1 day' AS INTERVAL)(IIRC). YMMV w sprawie istnienia i zachowania DATEoraz INTERVAL.

— po prostu ktoś

@FrozenFlame jest poprawny, odpowiedź nie działa bez rzutowania ciągów na typy dat. Wciąż brakuje jednej obsady. należy ::DATEdodać pierwszą część klauzuli

— where

Nie WHERE update_date::date = '2013-05-03' działałby tak dobrze, a może nieco bardziej czytelny?

— MikeF

@MikeF OP powiedział, że update_datebył timestamp without timezone. założyłem indeks w tej kolumnie. Twój predykat nie używałby tego indeksu.

— tylko ktoś

46

Podczas porównywania update_date >= '2013-05-03'postgres rzutuje wartości na ten sam typ, aby porównać wartości. Twój „2013-05-03” został przesłany na „2013-05-03 00:00:00”.

Tak więc dla update_date = '2013-05-03 14:45:00' twoje wyrażenie będzie takie:

'2013-05-03 14:45:00' >= '2013-05-03 00:00:00' AND '2013-05-03 14:45:00' <= '2013-05-03 00:00:00'

Tak jest zawsze false

Aby rozwiązać ten problem, wyślij update_date na date:

select * from table where update_date::date >= '2013-05-03' AND update_date::date <= '2013-05-03' -> Will return result

— Nicolai
źródło

1

rzutowanie co update_datew tabeli w porównaniu do rzutowania pojedynczej wartości parametru zapytania jest strasznie nieefektywne i zapewnia, że serwer nie będzie w stanie wykorzystać indeksów w tej kolumnie. kusi mnie -1 do tego.

— tylko ktoś

3

Tak, zgadzam się, że rzutowanie każdej wartości jest nieefektywne i możesz dać -1 dla tego rozwiązania. Ale opisałem przyczynę problemu i podałem przykład, który pokazuje problem. Teraz użytkownik 2866264 wie, dlaczego jego zapytanie nie zwraca oczekiwanych wierszy i zdecyduje, które rozwiązanie jest lepsze dla jego unikalnego przypadku.

— Nicolai,

@Nicolai: Bardzo dziękuję za odpowiedź. Działa zgodnie z odpowiedzią. Dziękuję również za wyjaśnienie.

— user2866264,

1

@Nicolai - Biorąc pod uwagę to, co powiedziałeś o rozszerzeniu Postgres dosłownie do kreski północy, jeśli celem jest znalezienie rekordów oznaczonych w jednym dniu (3 maja), czy ten kod byłby poprawny i bardziej wydajny: SELECT * FROM my_table WHERE update_date >= '2013-05-03' AND update_date < '2013-05-04'; (Zwróć uwagę na użycie 4 maja zamiast 3 i ze ZNAKIEM MNIEJSZYM NIŻ niższym niż lub równym.)

— Basil Bourque

9

Użyj tego rangetypu. Jeśli użytkownik wprowadzi datę:

select *
from table
where
    update_date
    <@
    tsrange('2013-05-03', '2013-05-03'::date + 1, '[)');

Jeżeli użytkownik wprowadzi znaczniki czasu wtedy nie trzeba się ::date + 1udział

http://www.postgresql.org/docs/9.2/static/rangetypes.html

http://www.postgresql.org/docs/9.2/static/functions-range.html

— Clodoaldo Neto
źródło

To zwięzła i interesująca odpowiedź!

— yuriploc

2

Użyj funkcji Konwertuj datę, aby porównać z datą: Wypróbuj to:

select * from table 
where TO_DATE(to_char(timespanColumn,'YYYY-MM-DD'),'YYYY-MM-DD') = to_timestamp('2018-03-26', 'YYYY-MM-DD')

— Yenky Bustamante
źródło