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
@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
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
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
Answered by Clodoaldo Neto
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
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