Support ICU extension and timezones for TIMESTAMPTZ converted to POSIXct and POSIXlt

[ggrothendieck]

This gives back a UTC time zone even if the original was in the local time zone. duckdb has an icu extension and TIMESTAMPTZ type which may solve this. Not sure.

library(duckdb)
con <- dbConnect(duckdb())
d <- data.frame(d = as.POSIXct("2000-01-01 12:13:14"))
duckdb_register(con, "d", d)
out <- dbGetQuery(con, "from d")
##                     d
## 1 2000-01-01 17:13:14

dput(out)
## structure(list(d = structure(946746794, class = c("POSIXct", 
## "POSIXt"), tzone = "UTC")), class = "data.frame", row.names = c(NA,  <--------------- note UTC
## -1L))

dput(d)
## structure(list(d = structure(946746794, class = c("POSIXct", 
## "POSIXt"), tzone = "")), class = "data.frame", row.names = c(NA, 
## -1L))

[eitsupi]

You may want to see the document:
https://r.duckdb.org/reference/duckdb.html#arg-timezone-out

[ggrothendieck]

Thanks. I will use that in the meantime but ideally POSIXct objects would be represented in duckdb as duckdb types that handle time zones in order to eliminate the impedance.

Note that that link claims that only UTC is handled by duckdb but I think that that only applies to TIMESTAMP type but not TIMESTAMPTZ type.

[krlmlr]

The TIMESTAMPTZ type from the ICU extension isn't currently handled properly. We'd need to fix that before even thinking about handling POSIXct . PR welcome!

library(duckdb)
#> Loading required package: DBI
con <- dbConnect(duckdb())

dbExecute(con, "CREATE TABLE x (a TIMESTAMPTZ)")
#> [1] 0
dbExecute(con, "INSERT INTO x VALUES ('2024-01-10 13:03:12 -0800'::TIMESTAMP)")
#> Error: rapi_prepare: Failed to prepare query INSERT INTO x VALUES ('2024-01-10 13:03:12 -0800'::TIMESTAMP)
#> Error: Conversion Error: timestamp field value "2024-01-10 13:03:12 -0800" has a timestamp that is not UTC.
#> Use the TIMESTAMPTZ type with the ICU extension loaded to handle non-UTC timestamps.
#> LINE 1: ...O x VALUES ('2024-01-10 13:03:12 -0800'::TIMESTAMP)
#>                                                   ^
dbExecute(con, "INSERT INTO x VALUES ('2024-01-10 13:03:12'::TIMESTAMP)")
#> [1] 1

# dbExecute(con, "INSTALL icu")
dbExecute(con, "LOAD icu")
#> [1] 0
dbExecute(con, "INSERT INTO x VALUES ('2024-01-10 13:03:12 -0800'::TIMESTAMPTZ)")
#> [1] 1
dbExecute(con, "INSERT INTO x VALUES ('2024-01-10 13:03:12 -0500'::TIMESTAMPTZ)")
#> [1] 1
dbReadTable(con, "x")$a
#> [1] "2024-01-10 13:03:12 UTC" "2024-01-10 13:03:12 UTC"
#> [3] "2024-01-10 13:03:12 UTC"

^{Created on 2024-08-16 with reprex v2.1.0}