date_arithmetic: use function in seconds

[HoneyryderChuck]

Hi 👋 ,

I have a variation of the following expression:

@ds.where(id: id).update(execute_at: Sequel.date_add(Sequel::CURRENT_TIMESTAMP, seconds: Sequel.function(:POWER, Sequel[:tab][:attempts] + 1,  4))

# attempting to build the following SQL in postgreSQL:
# ... , "executed_at" = (CAST(CURRENT_TIMESTAMP AS timestamp) + CAST(POWER("tab"."attempts" + 1, 4)))

which is blowing up with the following message:

Sequel::DatabaseError: PG::InvalidDatetimeFormat: ERROR:  invalid input syntax for type interval: "#<Sequel::SQL::NumericExpression:0x000000010a904b28> seconds "
LINE 1: ...t" = (CAST(CURRENT_TIMESTAMP AS timestamp) + CAST('#<Sequel:...

Is this a bug? If it isn't, what's the best way to translate the function call into smth the date functions can parse and use to build SQL?

[jeremyevans]

The date_arithmetic extension currently expects :seconds and similar arguments to be values, not arbitrary expressions. I might be able to change that on PostgreSQL using make_interval. I'll look into that today.

[jeremyevans]

OK, that is implemented in f78d7e5. Can you give the master branch a try and let me know whether it works for you?

[HoneyryderChuck]

hi Jeremy, thx for the patch. Can confirm it works! 👍

One thing though: Sequel.date_add(Sequel::CURRENT_TIMESTAMP, seconds: Sequel.function(:POWER, Sequel[@table][:attempts] + 1, 4)) produces the following SQL:

 (CAST(CURRENT_TIMESTAMP AS timestamp) + make_interval(secs := (0 + POWER(("outbox"."attempts" + 1), 4))))

which is technically correct, but that 0 + doesn't seem to belong there. Is it necessary?

[jeremyevans]

Attempting to avoid it would cause additional internal complexity. Considering it doesn't cause any problems (I assume the query optimizer ignores it), and usage of arbitrary expressions for the interval values is uncommon, I didn't think it worth it to try to avoid it in the SQL.

[HoneyryderChuck]

fair enough. I also agree it should not cause a particular issue. thx again!