How to add intervals to dates?

[ablygo]

I was trying to figure out how to add intervals to the SqlDate or SqlTimestamp types, but I couldn't find any interval types, or anything in Opaleye.Operators. Is this implemented anywhere?

[tomjaguarpaw]

Are you looking for SqlRange?

https://hackage.haskell.org/package/opaleye-0.7.1.0/docs/Opaleye-Operators.html#g:11

https://hackage.haskell.org/package/opaleye-0.7.1.0/docs/Opaleye-Internal-PGTypesExternal.html#t:SqlRange

[ablygo]

I don't quite think so, though I'm new to using PostgreSQL. I'm looking for a way to add interval as shown in https://www.postgresql.org/docs/9.1/functions-datetime.html, but I think SqlRange corresponds to ranges as shown in https://www.postgresql.org/docs/9.3/rangetypes.html. What I'm ultimately trying to do is write an update statement where an integer field (representing a number of days) is added to a date to result in a new date, as shown in the first link, but I can't find an operator to do that.

[tomjaguarpaw]

Ah I see. Should be straightforward to add. I'd be willing to accept a PR if you can make one.

If you look in Opaleye.Operators and see how other operators like .-|- are defined then you can probably work out how to add addDateDays along the same lines, and export it under "Other operators". Here's an implementation that might work:

addDateDays :: F.Field T.SqlDate -> F.Field T.SqlInt4 -> F.Field T.SqlDate
addDateDays = C.binOp (HPQ.:+)

Please let me know if you have any difficulty with this.

[ablygo]

If it really is just that one line I can do that; testing things directly in PostgreSQL it does seem like integers and dates sum together essentially like that. Puzzling through the library I assume though that C.binOp is basically just the SQL version of unsafeCoerceing a SQL binary operator to operate on different types in the Haskell interface? Assuming you would want me to test that the Opaleye functionality actually does what I think it should do will probably be a little while just to work through some other type errors I'm getting first.

[tomjaguarpaw]

If you're getting type errors feel free to past them here and I can have a look.

[basvandijk]

I also need support for intervals so I'm working on a PR that adds an Interval type to opaleye and associated polymorphic addInterval and minusInterval operations.

[basvandijk]

@tomjaguarpaw, @ablygo #519 is ready for review.