Обсуждение: 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.
>