Thanks for the clarification.
On Mon, Dec 4, 2017 at 4:15 PM, Francisco Olarte
wrote:
> On Mon, Dec 4, 2017 at 10:18 AM, 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.
>
