sqldf loses timezone information

[RoyalTS]

Possibly related to this sqldf seems to be losing timezone information. A reproducible example:

library(sqldf)
library(lubridate)
before <- data.frame(ct_sys = force_tz(Sys.time(), tzone='UTC')); before$ct_sys
after <- sqldf('select * from before', drv='SQLite'); after$ct_sys

It isn't so much that the time changes but that the timezone associated with the input data.frame gets changed in the output:

> library(sqldf)
> library(lubridate)
> before <- data.frame(ct_sys = force_tz(Sys.time(), tzone='UTC')); before$ct_sys
[1] "2016-05-25 19:15:52 UTC"
> after <- sqldf('select * from before', drv='SQLite'); after$ct_sys
[1] "2016-05-25 21:15:52 CEST"

[walkenhorst]

I came across the same issue, the fix I came up with is not a particularly great one, but it works.
First I store the original time zone, and set the R environment time zone to UTC:
originalTZ = Sys.timezone()
Sys.setenv(TZ = "UTC")

Then I execute my sqldf query.
Then I reset the time zone back to the local time zone:
Sys.setenv(TZ = originalTZ)

Hoping someone comes up with a better solution to this, like passing in a parameter to sqldf to control time zone conversion.

[ggrothendieck]

We can also use alternate method values to work around this.

1) First consider method = "name__class". Suppose we have a column that is in the LA time zone. Then we define as.la to convert a number to a POSIXct value in the LA time zone and add __la (two underscores) to the end of the returned column name.

DF <- data.frame(x = as.POSIXct("2000-01-01", tz = "America/Los_Angeles"))
as.la <- function(x) structure(x, tzone = "America/Los_Angeles", class = c("POSIXct", "POSIXt"))
out <- sqldf("select x x__la from DF", method = "name__class")
dput(out$x)
## structure(946713600, tzone = "America/Los_Angeles", class = c("POSIXct", "POSIXt"))

2) We can alternately specify for method a vector as long as the output number of columns specifying the classes to use. Assuming DF and as.la have been defined as above:

sqldf("select x from DF", method = "la")

3) Another alternative, not shown here, is to specify a function for method which processes the output columns,