Arithmetic operators don't represent SQL overloading

[mossprescott]

In SQL (Postgres, at least), arithmetic operators such +, -, *, and / are overloaded for date/time types. for example, the expression timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00 results in interval '1 day 15:00:00' (see https://www.postgresql.org/docs/9.2/interactive/functions-datetime.html).

However, Esqueleto defines (-.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a), which will generate the SQL needed to subtract timestamps, but gives the result the wrong type. Letting these operators be applied to just any type is incorrect anyway (e.g. you can't subtract strings in SQL), and forcing the argument and result types to all be the same is both unsound and prevents valid queries from being written.

I'm not sure how this mess would best be represented in Esqueleto, but perhaps a set of additional operators, including one for (TimeLike a, TimeLike b, IntervalLike c) => SqlExpr (Value a) -> SqlExpr (Value b) -> SqlExpr (Value c) amongst several others. I'm not sure how many types would inhabit TimeLike, IntervalLike, etc. This isn't sounding super promising.

Also open to gross hacks that let me do the unsafe cast or something.

[parsonsmatt]

You can definitely work around this with, eg,

veryUnsafeCoerceSqlExpr :: SqlExpr (Value a) -> SqlExpr (Value b)

veryUnsafeCoerceSqlExpr (time1 -. time2) :: SqlExpr (Value Interval)

:sweat_smile:

[mossprescott]

I'm intrigued, but that doesn't compile. There's veryUnsafeCoerceSqlExprValue but it's not exported. Can I write my own?

[parsonsmatt]

That should be exported, if from an internal module - I don't want anything hidden in this package.

[mossprescott]

I tried the other thing that should have been obvious, and it compiles:

    subtractTimestamps :: SqlExpr (Value UTCTime) -> SqlExpr (Value UTCTime) -> SqlExpr (Value NominalDiffTime)
    subtractTimestamps = unsafeSqlBinOp "-"

Presumably this will work for me for now. If I find out otherwise I'll update here.

[Vlix]

The - operator is used for tons of types in PostgreSQL. All sorts of time types, geometric types, json, network addresses, etc. These are not SQL-wide, but very PostgreSQL specific, so it'd make more sense for the (-) :: Num a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a) to be the standard one, and any specific implementation that has more, can be put in seperate modules.