Timestamp with time zone type conversion from PostgreSQL to R

    # 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 at piskorski.com>
http://www.piskorski.com/