On Mon, Dec 4, 2017 at 10:18 AM, <abedi0501@gmail.com> wrote:
> I'm noticing errors during date comparison,
> --Below works fine
> select '99999-01-01'::date > now();
> ?column?
> ----------
> t
> (1 row)
> --Gives error
> select '999999-01-01'::date > now();
> ERROR: date out of range for timestamp
>
> Database allows to store date ranges until 5874897 AD '5874897-01-01'::date
> but date comparison fails.
Seems a failure in reading comprehension, more than a bug. Notice the
error you pasted it says "out of range FOR TIMESTAMP"
When comparing dates to timestamps dates are converted to timestamp,
the greater precision type, because otherwise you would get erroneous
results. Not in your particular case ( as conversion to date truncates
down ) but it would in the other sense:
2017-01-01::date < 2017-01-01 10:00:00 timestamp > true => date is
upgraded adding 00:00:00
If you convert timestmap to date it would be false ( they would be equal ).
if you read https://www.postgresql.org/docs/10/static/datatype-datetime.html
you'll notice year in timestamps only covers 4713 BC to 294276 AD.
Cast now() to date or use current_date to avoid that:
test=> select '999999-01-01'::date;
date
--------------
999999-01-01
(1 row)
test=> select '999999-01-01'::date::timestamp;
ERROR: date out of range for timestamp
test=> select '999999-01-01'::date > now();
ERROR: date out of range for timestamp
test=> select '999999-01-01'::date > now()::date;
?column?
----------
t
(1 row)
test=> select '999999-01-01'::date > current_date, current_date;
?column? | date
----------+------------
t | 2017-12-04
(1 row)
Francisco Olarte.