[R-sig-DB] Timestamp with time zone type conversion from PostgreSQL to R

[Andrew Piskorski]

On Tue, Mar 09, 2010 at 09:56:40AM +0100, Koen Stegen wrote:

>     # The input: any day, midnight, in UTC
>     query = "SELECT TIMESTAMP WITH TIME ZONE '1999-12-31 00:00:00 UTC'"
>     result = dbGetQuery(databaseConnection, query)
> 
>     # The problem:
>     strftime(result$timestamptz, usetz=TRUE)  # prints:"1999-12-31 01:00:00 UTC"
>     as.numeric(result$timestamptz)            # prints: 946602000
> 
> The value of strftime is 1 am instead of midnight. The numeric value is 3600
> seconds higher than I expect, so it is not *just* a display problem.

Koen, I don't know if it's related to your problem or not, but R,
without any use of PostgreSQL at all, seems to sometimes have trouble
correctly formatting POSIX time values.  Note:

  > as.integer(Sys.time()) 
  [1] 1269544425 
  > format(Sys.time() ,'%Y-%m-%d %T %z') 
  [1] "2010-03-25 15:13:45 +0000" 
  > format(Sys.time() ,'%Y-%m-%d %T %Z') 
  [1] "2010-03-25 15:13:45 EDT" 

That's with:
  R 2.9.2 (Patched), 2009-09-24, svn.rev 50183, x86_64-unknown-linux-gnu

I am currently in the EDT (Eastern Daylight Time) timezone, and the
output of the "%Z" format above is correct.  However, the "%z" is
wrong, EDT is UTC -4, not +0.

-- 
Andrew Piskorski <atp using piskorski.com>
http://www.piskorski.com/