Goal:
Have a column (which doesn't exist in the table) representing the number of seconds left until the next time a recurring event is to take place, which is determined by "last done" (timestamptz) combined with the "time span string" column, which is a string such as "7 days" or "25 minutes" or "2 weeks" (standard PG compatible interval strings).
The query:
SELECT
*,
now() - "last done" - "time span string"::interval AS "seconds left until next"
FROM "recurring events"
ORDER BY "seconds left until next" DESC NULLS LAST;
The "seconds left until next" column bizarrely gets values such as:
-1 days +00:32:04.389802
Expected value example:
123456
I don't understand why it's not calculating with the columns. It seems to be constructing a string of their values in an interpreted manner or something. I don't know how to describe it.
I've tried to ::integer the AS expression, but that just gives me the error:
ERROR: cannot cast type interval to integer
The question isn't so much about the correct "formula"/order of the actual expression as the fact that it doesn't turn the column (or cell, rather) into an integer (representing the number of seconds left for that event until the next time).
now() - "last done" - "time span string"::interval
In Postgres' date arithmetics, substracting timestamps generates a data of type interval, which is what you are seeing in the result.
You can turn this to seconds with extract():
extract(epoch from (now() - "last done" - "time span string"::interval))