new types in postgresqlDataType

[jangorecki]

trafficstars

Below the part of postgresqlDataType. I've added two types IDate and ITime.

 sql.type <- switch(rs.class,
                           character = "text",
                           logical = "bool",
                           factor = "text",
                           ordered = "text",
                           IDate=,Date= "date",
                           ITime = "time",
                           POSIXct = "timestamp with time zone",
                           "text")

I can save new data type from R but I cannot read it.

library(RPostgreSQL)
library(data.table)
Sys.setenv(TZ="UTC")
invisible(Sys.setlocale("LC_TIME", "C"))
drv = dbDriver("PostgreSQL")
conn = dbConnect(drv = drv, ...) # db configured on UTC
px = as.POSIXct(c("2015-10-12 00:10:15","2015-10-12 02:10:15"), origin="1970-01-01")
dt = IDateTime(px)[, px := px]
str(dt)
#Classes ‘data.table’ and 'data.frame': 2 obs. of  3 variables:
# $ idate: IDate, format: "2015-10-12" "2015-10-12"
# $ itime:Class 'ITime'  int [1:2] 615 7815
# $ px   : POSIXct, format: "2015-10-12 00:10:15" "2015-10-12 02:10:15"
dbWriteTable(conn, "tmp", dt)
df = dbGetQuery(conn, "select * from tmp;")
str(df)
#'data.frame':  2 obs. of  4 variables:
# $ row.names: chr  "1" "2"
# $ idate    : Date, format: "2015-10-12" "2015-10-12"
# $ itime    : chr  "00:10:15" "02:10:15"
# $ px       : POSIXct, format: "2015-10-12 00:10:15" "2015-10-12 02:10:15"

While table in db created by dbWriteTable

CREATE TABLE tmp
(
  "row.names" text,
  idate date,
  itime time without time zone,
  px timestamp with time zone
)

Ideally would be to have ITime-time conversion on R-DB, and consistent behaviour to POSIXct to produce time with time zone. Where in the code I should look for such change?

[jangorecki]

For the time with time zone it should be enough to update switch and define type the same way as posixct. But how can I add support for reading those types from db?