[R-sig-DB] RPostgreSQL driver timestamp data type mapping

[Prof Brian Ripley]

On Tue, 12 Jan 2010, Sebastian P. Luque wrote:

> Hi,
>
> Using the RPostgreSQL driver to access a view on a PostgreSQL 8.4
> database:
>
> library(RPostgreSQL)
> m <- dbDriver("PostgreSQL")
> con <- dbConnect(m, user="me", password="pwd", dbname="dbname")
> qry <- "SELECT * FROM mytable"
> rs <- dbSendQuery(con, qry)
>
> Fields corresponding to timestamp data type are mapped to POSIXct, as
> shown by dbColumnInfo():
>
>        name    Sclass      type len precision scale nullOK
> ...
> ...
> 8  date_time   POSIXct TIMESTAMP   8        -1    -1   TRUE
>
> I'd like to have a bit more control over this conversion, as it
> currently results in the POSIXct object having the time zone of my
> locale,

Not really: POSIXct times are absolute times: it is when they are 
displayed that you see the locale.  So just add a tzone attribute to 
the result, as in

> x <- Sys.time()
> x
[1] "2010-01-13 07:07:26 GMT"
> attr(x, "tzone") <- "PST8DST"
> x
[1] "2010-01-12 23:07:26 PST"
> attr(x, "tzone") <- NULL
> x
[1] "2010-01-13 07:07:26 GMT"


> whereas I need to set it to GMT.  Reading the DBI manual, I
> can't see how this could be done.  Any pointers would be appreciated.
> Thanks.
>
>
> Cheers,
>
> -- 
> Seb
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595