How to compare dates in datetime fields in Postgresql?

Question

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows).

I have a column in my table say update_date with type timestamp without timezone.
Client can search over this field with only date (e.g: 2013-05-03) or date with time (e.g.: 2013-05-03 12:20:00).

This column has the value as timestamp for all rows currently and have the same date part 2013-05-03, but difference in time part.

When I'm comparing over this column, I'm getting different results. Like the followings:

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 question is how can I make the first query possible to get results, I mean why the 3rd query is working but not the first one?

Spiv

Just for reference, check some useful date/time functions and operators such as NOW() + INTERVAL '1 hour' – Goshorn

Answer 1

@Nicolai is correct about casting and why the condition is false for any data. i guess you prefer the first form because you want to avoid date manipulation on the input string, correct? you don't need to be afraid:

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

Runoff

Is this syntax ('2013-05-03'::date and '1 day'::interval) PostgreSQL specific? – Analects

@FrozenFlame yes it is. the standard syntax would be CAST('2013-05-03' AS DATE) + CAST('1 day' AS INTERVAL) (IIRC). YMMV on the existence and behavior of DATE and INTERVAL. – Runoff

@FrozenFlame is correct, the answer does not work without casting the strings to date types. One cast is still missing. there needs to be a ::DATE added to the first part of the where clause – Pasquale

Wouldn't WHERE update_date::date = '2013-05-03' work as well and maybe slightly more readable? – Hallam

@Hallam OP said update_date was timestamp without timezone. i assumed an index on that column. your predicate would not use that index. – Runoff

@just somebody I read your comment below and understand. Thanks for pointing that out. – Hallam

This answer was edited based on @StillLearningToCode's comment. But I think that it should be reverted to its original form. I tested with postgres 12 and the string is indeed cast implicitly as suggested by Nicolai. Explicitly casting '2013-05-03'::date in the first part of the where clause is redundant and adds some confusion. In trying to understand its purpose, I initially thought that it nudged postgres to also cast update_date to date and I couldn't understand in what way this is more efficient than Nicolai's suggestion. The original form is more obvious in what is going on. – Pruett

Answer 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 false

To solve this problem cast update_date to date:

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

Kop

casting every update_date in the table vs. casting the single value of the query parameter is terribly inefficient, and makes sure the server won't be able to leverage indexes on that column. i'm tempted to -1 this. – Runoff

Yes, I agree that casting of each value is inefficient and you may give -1 for this solution. But I described the reason of the problem and had given example that demonstrates the issue. Now user2866264 know why his query doesn't return expected rows and will decide what exactly solution is better for his unique case. – Kop

@Nicolai: Thanks a lot for your answer. It works by following your answer. Also thanks for the explanation. – Spiv

@Kop – Given what you said about Postgres expanding the date literal to stroke of midnight, if the goal is finding records marked on a single date (May 3rd), would this code be correct and more efficient: SELECT * FROM my_table WHERE update_date >= '2013-05-03' AND update_date < '2013-05-04'; (Note the use of May 4th rather than 3rd and with a LESS-THAN SIGN rather than less-than-or-equal.) – Wrongdoing

Answer 3

Use the range type. If the user enter a date:

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

Turnery

That's a succinct and interesting answer! – Lorielorien

Answer 4

Use Date convert to compare with date: Try This:

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

Mcglothlin

Answer 5

You can also use BETWEEN operator.

Here's a simple example:

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

You can also pick everything that is not between 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 advanced example, involving timestamp delta based on days:

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

Jezabel

Answer 6

CAST("FieldDate" AS TIMESTAMP) > '2023-12-07 00:01:23.786'::timestamp

This has been worked properly for me

Racism