Search code examples
rdplyrtimestampdbplyr

Cast timestamps to timestamptz using mutate_if

I have a database with timestamps (no timezone) that I would like to cast to timestamptz (with timezone) prior to using collect(). So far, I've tried these:

db_tbl %>% mutate_if(lubridate::is.timepoint, funs(CAST))

But, I can't figure out how to add AS timestamptz to this function call

It gives me:

Error in result_create(conn@ptr, statement) : 
  Failed to prepare query: ERROR:  syntax error at or near ")"
LINE 1: SELECT "user_id", "time_zone", CAST("confirmed_at") AS "conf...

and

db_tbl %>% mutate_if(lubridate::is.timepoint, funs(sql(paste0(., "::timestamptz"))))

But, I can't figure out how to get the paste0() to execute instead of being translated into CONCAT: It gives me:

Applying predicate on the first 100 rows
<SQL>
SELECT "user_id", "time_zone", CONCAT_WS('', "confirmed_at", '::timestamptz') AS "confirmed_at"
FROM (SELECT "user_id", "time_zone", "confirmed_at"
FROM app.app_users) "paiaayosfl"

Ultimately, I'm trying to pull timestamps in without R assuming my local timezone.

Solution

  • For our future descendants:

      .data %>%
    mutate_if(lubridate::is.timepoint, funs(CAST(. %as% timestamptz)))