How to compare dates in datetime fields in Postgresql?

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?

5 Answers

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

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 

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

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

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