Thread: BUG #14944: Error for 6 digit year in date comparision

The following bug has been logged on the website:

Bug reference:      14944
Logged by:          Akash Bedi
Email address:      abedi0501@gmail.com
PostgreSQL version: 9.5.4
Operating system:   Ubuntu 16/Windows 10
Description:

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.

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.

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. >