Sqldf: change timestamp from local time to GMT / UTC

Question

On my machine, sqldf changes POSIXct from local time (I'm on CST, GMT -0600) to GMT / UTC. Is this expected behavior? Can I stop R or sqldf from doing this? Here is my code:

> library('sqldf') > > before <- data.frame(ct_sys = Sys.time()) > before ct_sys 1 2012-03-01 12:57:58 > after <- sqldf('select * from before') > after ct_sys 1 2012-03-01 18:57:58 > 

Answer 1

This seems to be a timezone error in sqldf. It should be fixed now in sqldf version 0.4-6.2 (not yet on CRAN, but you can try it this way):

 library(sqldf) source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") before <- data.frame(ct_sys = Sys.time()); before after <- sqldf('select * from before'); after 

Even without this, sqldf("select * from before", method = "raw") will work, although it will return a numeric column (which you can convert to POSIXct) and may affect other columns. The following is an example using method = "raw" . Note that in both cases we get 1330661786.181 :

 > library(sqldf) > > before <- data.frame(ct_sys = Sys.time()); dput(before) structure(list(ct_sys = structure(1330661786.181, class = c("POSIXct", "POSIXt"))), .Names = "ct_sys", row.names = c(NA, -1L), class = "data.frame") > > after <- sqldf('select * from before', method = "raw"); dput(after) structure(list(ct_sys = 1330661786.181), .Names = "ct_sys", row.names = 1L, class = "data.frame") 

EDIT: Added example using method = "raw"