Thread

Re: ago(interval) → timestamptz

Florents Tselai <florents.tselai@gmail.com> — 2025-11-06T11:15:16Z

> On 6 Nov 2025, at 10:37 AM, Andreas Karlsson <andreas@proxel.se> wrote:
> 
> On 11/4/25 6:55 AM, Laurenz Albe wrote:
>> Moreover, a good percentage of the users would instead need ago(interval) -> timestamp.
> 
> I don't get what users would need ago(interval) -> timestamp. That function would not make any sense since there is no equivalent to now() which returns timestamp, simply because a timestamp does not refer to any specific point in time and can only be interpreted with some additional piece of information like a time zone.

I agree that only a timestamptz variant makes sense.

> 
> That said I can't get too excited about this patch since it is just a shorter way to write e.g. now() - interval '1 day'. It would also be quite funny to see all uses of ago('-1 day') for tomorrow.

I’m mostly aiming for scenarios like this: 

WHERE ts BETWEEN ago('10 days') AND now() 

is probably more readable than 

WHERE ts BETWEEN now() - interval '10 days' AND now()

This shorthand can remove a lot of mental arithmetic ("subtract interval X”);
such arithmetic can easily compound in non-trivial analytical queries involving multiple filters.

But yeah, most of the (counter) arguments I think have been layed out.

Is it syntactic sugar? Yes. 
Does it reduce cognitive load and improve readability? I think so. 
Is it worth having in core? Maybe not, but then why not?  
IMHO I don't see much downside other than one more entry in the docs.

For context, below are 3 instances of other systems that offer this function

- https://docs.aws.amazon.com/timestream/latest/developerguide/date-time-functions.html
- https://learn.microsoft.com/en-us/kusto/query/ago-function
- https://docs.firebolt.io/reference-sql/functions-reference/date-and-time/ago