Time operations in postgresql backend don't seem to support arithmetic

[iand675]

Hi there,

I've recently started experimenting with beam, and it's really a pleasure to use so far!

However, I've run into an issue regarding performing arithmetic on times: I can't seem to perform subtraction in a sane way. In pure Haskell, using the canonical time library, we have NominalDiffTime, which supports addUTCTime and diffUTCTime in order to work around addition and subtraction being against seconds rather than another UTCTime. I think there would need to be something similar in this library to support these sorts of cases, as time is often used in databases.

Here's a small chunk of code that demonstrates my current dilemma, in case I'm missing something obvious:

data IdempotencyKeyT f = IdempotencyKey
  { idempotencyKeyId :: C f (Auto Int64)
  , idempotencyKeyCreatedAt :: C f (Auto UTCTime)
  } deriving (Generic)

... boilerplate

localTimestamp :: QExpr PgExpressionSyntax s UTCTime
localTimestamp = customExpr_ "localtimestamp"

runDelete $ delete (dbIdempotencyKeys dbSettings) $ \key ->
  idempotencyKeyCreatedAt key <. auto_ 
    (localTimestamp `subE` (val_ aDiffTimeGoesHere))

Thanks in advance for any suggestions you might have!

[tathougies]

Hello!

You are right, there ought to be manipulation functions for date/time types. Unfortunately, SQL is has a comparatively rich date, time and interval type set compared to haskell, and the parallels aren't obvious, so some thought needs to be put into it. I'm planning on better date time support in the next beam-core release. You ought to be able to do what you want or now using customExpr_:

diffUTCTime_ :: QGenExpr ctxt PgExpressionSyntax s UTCTime -> QGenExpr ctxt PgExpressionSyntax s NominalDiffTime -> QGenExpr ctxt PgExpressionSyntax s UTCTime
diffUTCTime_ = customExpr_ (\tm offs -> "(" <> tm <> " - INTERVAL '" <> offs <> " SECONDS')")

Or something like that.

And I'm always happy to hear input on sane SQL -> Haskell type mappings, especially regarding the INTERVAL type in SQL.

[tathougies]

Ian,

With regards to 'DELETE ... FROM ... RETURNING', that syntax isn't supported yet, but it actually shouldn't be difficult to add, if you're willing to put some work in. You can look at the implementations of 'pgInsertReturning' and 'pgUpdateReturning' to guide you.

The 'WITH' construct would be more difficult, but I'm working on it :)

Travis

On Mon, Feb 5, 2018 at 9:13 PM, Ian Duncan [email protected] wrote:

P.S., any advice on using subselects in deletes / returning data from them? The ideal query I'd have would be something like:

WITH deleted AS (DELETE FROM idempotency_keys WHERE id IN (SELECT id FROM idempotency_keys WHERE created_at < (localtimestamp - ) LIMIT ?) RETURNING ) SELECT count() FROM deleted

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/tathougies/beam/issues/131#issuecomment-363313179, or mute the thread https://github.com/notifications/unsubscribe-auth/AATn8sbuTS-ZtJteg1pXyT3U0XYk8VVYks5tR999gaJpZM4R6ev9 .

[iand675]

@tathougies that's great to hear. I don't suppose you're interested in any feature bounties? I might be able to make a case for my employer to sponsor a few enhancements along these lines.

[fabioluz]

Are there any updates on this? I'm facing almost the same issue, I would like to subtract 2 dates but one of them is an argument, like: diffUTCTime_ (createdAt table) (val_ someUtcTime)

[kmicklas]

@fabioluz We still don't have support for this yet but PRs are welcome!