How do you get the difference between two "timestamps with/without timezone" in seconds using dplyr?

Question

When a in-database (PostgreSQL) dplyr::mutate operation calculates the difference between two timestamps, a character vector is returned, each element of the form like:

> RPostgreSQL::dbGetQuery(db$con, 'select now() - current_date;')
         ?column?
1 09:23:48.880493

In this case it is HH:MM:SS.ssssss. How do I get dplyr to return this vector of time differences in seconds? That is, I would like to do the same thing as here, except have it as part of a mutate statement.

Example dplyr code would be:

tbl(db$con, 'tmp_table') %>%
    mutate(time_diff = received_at - started_at) %>%
    select(id, time_diff) %>%
    collect(n = Inf)

why not just `select extract(epoch from now() - current_date)` stright in SQL?.. — Vao Tsun, May 15 '17 at 09:44
I am using the `dplyr` language to build a complex query and I do not wish to translate the whole thing into SQL, also, as part of the pipe I copy data to the database from `R` in order to do joins. — Alex, May 15 '17 at 09:45

Alex · Answer 1 · 2017-05-15T10:05:08.757

This is by no means a satisfactory answer to me, but a roundabout way of doing it is:

tmp_table <-
tbl(db$con, 'tmp_table') %>%
    mutate(time_diff = received_at - started_at) %>%
    select(id, time_diff) %>%
    compute() # creates a temporary table

You can then find the name of the temporary table using:

as.character(tmp_table$ops$x$x)

In my case this was [1] "rzlhbxogjx". Then, using the linked answer you could do:

RPostgreSQL::dbGetQuery(db$con, 
              paste0("select id, extract(epoch from time_diff) 
              as time_diff from ", as.character(tmp_table$ops$x$x), ";")

How do you get the difference between two "timestamps with/without timezone" in seconds using dplyr?

1 Answers1