9

On my machine, sqldf changes a POSIXct from localtime(I'm at 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
>
Ryogi
  • 4,907
  • 5
  • 22
  • 43
mpettis
  • 2,468
  • 4
  • 19
  • 29

1 Answers1

3

That seems to be a time zone bug in sqldf. It should be fixed now in sqldf version 0.4-6.2 (not yet on CRAN but you can try it out like this):

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 doing that sqldf("select * from before", method = "raw") will work although that will return a numeric column (which you can convert to POSIXct) and may affect other columns too. Here is an example of 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 of using method = "raw"

G. Grothendieck
  • 211,268
  • 15
  • 177
  • 297
  • Thanks! The patch from the http source worked, and I will use that. The second one did convert to raw, but the raw number was still transformed to one that, when recast back to POSIXct, was in GMT. Thanks again, Matt. – mpettis Mar 02 '12 at 02:31
  • Suggest you check how you converted it back. As you can see from the code I added to the answer it gives identical numbers back. – G. Grothendieck Mar 02 '12 at 04:20