Postgres Pro
Downloads
Home   >   mailing lists

Thread: abstime bug

abstime bug

From
jw
Date:
# select '1901/12/14 1:00'::abstime;
         abstime
------------------------
  2038-01-19 07:22:24+08
(1 row)

Re: abstime bug

From
Bruce Momjian
Date:
jw wrote:
> # select '1901/12/14 1:00'::abstime;
>          abstime
> ------------------------
>   2038-01-19 07:22:24+08
> (1 row)

Current CVS shows:

    test=> select '1901/12/14 1:00'::abstime;
            abstime
    ------------------------
     1901-12-14 01:00:00-05
    (1 row)

What PostgreSQL version are you using?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: abstime bug

From
Michael Fuhr
Date:
On Fri, Jul 22, 2005 at 10:15:40AM -0400, Bruce Momjian wrote:
>
> Current CVS shows:
>
>     test=> select '1901/12/14 1:00'::abstime;
>             abstime
>     ------------------------
>      1901-12-14 01:00:00-05
>     (1 row)

Depends on your timezone:

SET TimeZone TO 'US/Eastern';
SELECT '1901/12/14 1:00'::abstime;
        abstime
------------------------
 1901-12-14 01:00:00-05
(1 row)

SET TimeZone TO 'Asia/Hong_Kong';
SELECT '1901/12/14 1:00'::abstime;
        abstime
------------------------
 2038-01-19 07:51:40+08
(1 row)

I'd guess this is due to the 32-bitness of abstime.  Those timestamps
are around the min and max values of a 32-bit timestamp based on the
traditional Unix epoch.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: abstime bug

From
Bruce Momjian
Date:
Michael Fuhr wrote:
> On Fri, Jul 22, 2005 at 10:15:40AM -0400, Bruce Momjian wrote:
> >
> > Current CVS shows:
> >
> >     test=> select '1901/12/14 1:00'::abstime;
> >             abstime
> >     ------------------------
> >      1901-12-14 01:00:00-05
> >     (1 row)
>
> Depends on your timezone:
>
> SET TimeZone TO 'US/Eastern';
> SELECT '1901/12/14 1:00'::abstime;
>         abstime
> ------------------------
>  1901-12-14 01:00:00-05
> (1 row)
>
> SET TimeZone TO 'Asia/Hong_Kong';
> SELECT '1901/12/14 1:00'::abstime;
>         abstime
> ------------------------
>  2038-01-19 07:51:40+08
> (1 row)
>
> I'd guess this is due to the 32-bitness of abstime.  Those timestamps
> are around the min and max values of a 32-bit timestamp based on the
> traditional Unix epoch.

Yea, I see the same thing in 8.0.X.  I don't think abstime should be
used in that date range, timestamp is a better solution.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: abstime bug

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Michael Fuhr wrote:
>> I'd guess this is due to the 32-bitness of abstime.  Those timestamps
>> are around the min and max values of a 32-bit timestamp based on the
>> traditional Unix epoch.

> Yea, I see the same thing in 8.0.X.  I don't think abstime should be
> used in that date range, timestamp is a better solution.

It's still a bug though; if the value is out of range, abstimein should
reject it, not misconvert it.

            regards, tom lane

Re: abstime bug

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> SET TimeZone TO 'Asia/Hong_Kong';
> SELECT '1901/12/14 1:00'::abstime;
>         abstime
> ------------------------
>  2038-01-19 07:51:40+08
> (1 row)

> I'd guess this is due to the 32-bitness of abstime.  Those timestamps
> are around the min and max values of a 32-bit timestamp based on the
> traditional Unix epoch.

Fixed in CVS tip:

regression=# SET TimeZone TO 'Asia/Hong_Kong';
SET
regression=# SELECT '1901/12/14 1:00'::abstime;
 abstime
---------
 invalid
(1 row)

Doesn't seem important enough to back-patch, though.

            regards, tom lane