How do do extract(epoch from interval)

[HoneyryderChuck]

Hi 👋 ,

I'm trying to build a query to calculate the median, based on the calculation of the interval between two timestamps (most of the examples around imply that the calculated value is already in a column).

I'm trying to build the following expression for the task:

SELECT percentile_count(0.5) WITHIN GROUP (ORDER BY (extract(epoch from ("records"."finish_at" - "records"."start_at")) / 3600 )) FROM ...

I've half done it but writing the following:

.select(Sequel.function(:percentile_count, 0.5).within_group(Sequel.function(:extract, :epoch, Sequel[:records][:finish_at] - Sequel[:records][:start_at]) / 3600))

but the following SQL is written:

SELECT percentile_count(0.5) WITHIN GROUP (ORDER BY (extract('epoch', ("records"."finish_at" - "records"."start_at")) / 3600 )) FROM ...

which is invalid:

Sequel::DatabaseError: PG::SyntaxError: ERROR:  syntax error at or near ","
LINE 1: ...(0.5) WITHIN GROUP (ORDER BY (extract('epoch from', ("records...
                                                           ^

Any suggestions on how to accomplish this?

[janko]

Is it possible that Sequel.extract would solve the issue?

[HoneyryderChuck]

Oh no, I actually thought I was using Sequel.extract, my brain went blank 🙈 . You're right, that works like a charm.