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
