Extract Epoch

[xnmp]

Hi everyone, I'm just wondering how to get Postgres functions in Opaleye, in particular how can I get the following?

SELECT
  project_id,
  avg(EXTRACT(EPOCH FROM (accept_timestamp - award_timestamp)))  avg_accept_time
FROM projects
GROUP BY 1;

Thanks in advance.

[tomjaguarpaw]

This hasn't been implemented in Opaleye. It could be implemented in a similar way to the existing functions in https://github.com/tomjaguarpaw/haskell-opaleye/blob/master/src/Opaleye/Operators.hs

[xnmp]

So how can I implement them? Is there an example somewhere? Thanks.

[xnmp]

Answering my own question: I did it like this:

epoch :: Column LocalTime -> Column Int
epoch = extract . epoch'
  where
    extract (Col.Column a) = Col.Column (HPQ.FunExpr "extract" [a])
    epoch' (Col.Column a) = Col.Column (HPQ.FunExpr "epoch from" [a])

[tomjaguarpaw]

Great! Isn't it slightly nicer to do

epoch :: Column LocalTime -> Column Int
epoch = Col.Column . extract . epoch' . unColumn
  where
    extract a = HPQ.FunExpr "extract" [a]
    epoch' a = HPQ.FunExpr "epoch from" [a]
    unColumn (Column a) = a

Would you be able to submit this as a PR?

[tomjaguarpaw]

This seems resolved. Feel free to reopen if not.