returned Date fields from dbGetQuery not rendering correctly in DT::datatable

[catlyst]

Dates do not display correctly (seems like some form of escaping has been applied), and spaces turn into T with a final Z at the end, as shown here:

However, within R, the data.table object looks fine:

This DT::datatable problem does not happen when the exact same query is used on clickhouse-r (my only other basis of comparison)

[vpanfilov]

@catlyst, this is definately NOT a RClickhouse problem. Look here for some info about formatting DateTime values in DT columns.

[catlyst]

Thank you for the link (which unfortunately didn't have the exact conversion I needed), in addition to a can of "date"d worms, it opened up my appreciation of the POSIXct datatype which is not fully supported in R, but with a combination of is.POSIXct() and adapting code from here, I managed to get the behaviour I needed.

Thanks again for your good work with Rclickhouse & patience in advising a noob like me.

For posterity (and to save folks in similar predicaments many minutes of googling) here was what worked for my specific purposes of having DT render POSIXct columns within my data.table as 'YYYY-MM-DD hh:mm:ss' instead of the default "Zulu time" ISO8601 spec:

is.POSIXct <- function(x) inherits(x, "POSIXct")
dt<-data.table(dbGetQuery(pool,sql))
dateCols<-names(Filter(is.POSIXct,dt))
dt<-dt[,(dateCols):=lapply(.SD,as.character), .SDcols=dateCols]