In Postgresql, how do you compare dates in datetime fields?

Problem

When comparing dates in Postgresql, I’ve run into an odd situation (version 9.2.4 in windows). In my database, I have an update date column with the type ‘timestamp without timezone.’ Clients can search this field using only date (e.g., 2013-05-03) or date and time (e.g., 2013-05-03). (i.e: 2013-05-03 12:20:00). This field presently has a timestamp value for all rows, with the same date portion (2013-05-03) but a different time part.

I’m getting various results when I compare this column. Consider the following:

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

My issue is how can I make the first query return results, i.e. why does the third query return results but not the first?

Asked by user2866264

Solution #1

@Nicolai is true in his assessment of casting and the reason why the condition is false for any data. I assume you prefer the first form because you don’t want to mess with the input string’s date, correct? You don’t have to be concerned:

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

Answered by just somebody

Solution #2

When you compare update_date >= ‘2013-05-03’ postgres casts values to the same type to compare values. So your ‘2013-05-03’ was casted to ‘2013-05-03 00:00:00’.

So for update_date = ‘2013-05-03 14:45:00’ your expression will be that:

'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'

This is always incorrect.

To overcome this issue, set update date to the current date:

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

Answered by Nicolai

Solution #3

The range type should be used. If the user enters a date, the following will happen:

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

If the user enters timestamps then you don’t need the ::date + 1 part

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

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

Answered by Clodoaldo Neto

Solution #4

To compare dates, use Date convert: Take a look at this:

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

Answered by Yenky Bustamante

Solution #5

The BETWEEN operator might also be used.

Here’s a simple illustration:

SELECT
    customer_id,
    payment_id,
    amount,
    payment_date
FROM
    payment
WHERE
    payment_date BETWEEN '2007-02-07' AND '2007-02-15';

You can also select anything that does not fall within these dates:

SELECT
    customer_id,
    payment_id,
    amount,
    payment_date
FROM
    payment
WHERE
    payment_date NOT BETWEEN '2007-02-07' AND '2007-02-15';

Here’s a more complex example involving a days-based timestamp delta:

SELECT
    api_project.name,
    api_project.created,
    survey_response.created AS response_date,
    CASE
        WHEN survey_response.created
            BETWEEN api_project.created AND
                   (api_project.created + INTERVAL '180 days')
            THEN 'first_6_months'
        ELSE '6_months_after'
    END AS when_it_was_answered,
    EXTRACT(DAYS FROM survey_response.created - api_project.created)
      AS days_since_response
FROM
    bfb_survey_surveyresponseppent

Answered by ivanleoncz

Post is based on https://stackoverflow.com/questions/19469154/how-to-compare-dates-in-datetime-fields-in-postgresql