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?

Just for reference, check some useful date/time functions and operators such as NOW() + INTERVAL '1 hour' — Ricardo
– Ricardo, Commented Mar 14, 2022 at 19:01

just somebody · Accepted Answer · 2016-11-12 17:50:19Z

536

@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);

edited Nov 12, 2016 at 17:50

answered Oct 19, 2013 at 18:45

just somebody

19.4k6 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Frozen Flame Over a year ago

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

just somebody Over a year ago

@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.

StillLearningToCode Over a year ago

@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

MikeF Over a year ago

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

just somebody Over a year ago

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

|

Denys Kurochkin · Accepted Answer · 2024-04-29 11:12:28Z

96

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

edited Apr 29, 2024 at 11:12

Denys Kurochkin

1,5611 gold badge21 silver badges39 bronze badges

answered Oct 19, 2013 at 17:58

Nicolai

5,8251 gold badge28 silver badges33 bronze badges

4 Comments

just somebody Over a year ago

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.

Nicolai Over a year ago

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.

user2866264 Over a year ago

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

Basil Bourque Over a year ago

@Nicolai – 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.)

Clodoaldo Neto · Accepted Answer · 2013-10-19 18:48:08Z

16

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

edited Oct 19, 2013 at 18:48

answered Oct 19, 2013 at 18:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

yuriploc Over a year ago

That's a succinct and interesting answer!

Simon White Apr 18 at 23:19

It worked for me, but I had to omit the last '[)' parameter to tsrange

Yenky Bustamante · Accepted Answer · 2018-04-24 15:10:03Z

11

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

answered Apr 24, 2018 at 15:10

Yenky Bustamante

1111 silver badge4 bronze badges

Comments

ivanleoncz · Accepted Answer · 2021-12-07 19:43:58Z

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

Salma Gomaa · Accepted Answer · 2023-12-07 07:42:44Z

3

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

This has been worked properly for me

answered Dec 7, 2023 at 7:42

Salma Gomaa

1,1121 gold badge17 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to compare dates in datetime fields in Postgresql?

6 Answers 6

7 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related