[BUGS] abstime bug

2005-07-22 Thread jw 

# select '1901/12/14 1:00'::abstime;
abstime

 2038-01-19 07:22:24+08
(1 row)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [BUGS] abstime bug

2005-07-22 Thread Bruce Momjian 
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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [BUGS] abstime bug

2005-07-22 Thread Michael Fuhr 
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/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [BUGS] abstime bug

2005-07-22 Thread Bruce Momjian 
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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] abstime bug

2005-07-22 Thread Tom Lane 
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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] abstime bug

2005-07-22 Thread Tom Lane 
Michael Fuhr [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org