Thread

ago(interval) → timestamptz

Florents Tselai <florents.tselai@gmail.com> — 2025-11-03T21:36:43Z

Hi,

I realize this will get some "you can easily implement this yourself”
pushback,
But I keep seeing and writing a lot of WHERE ts > now() - interval '1 day'
expressions.

Having $subject should help in such cases.
We already have now, and age, so we might as well have ago too.
Other systems also expose similar helpers.

Cheers,
Flo

Re: ago(interval) → timestamptz

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-04T05:55:47Z

On Mon, 2025-11-03 at 22:36 +0100, Florents Tselai wrote:
> I realize this will get some "you can easily implement this yourself” pushback,
> But I keep seeing and writing a lot of WHERE ts > now() - interval '1 day' expressions.

You can easily implement this yourself...
Also, there already is something similar in the shape of 'yesterday'::timestamptz.
Moreover, a good percentage of the users would instead need ago(interval) -> timestamp.

So I'd say that the added value is marginal, and I personally find

   current_timestamp - INTERVAL '1' DAY

more readable and more SQL standard compliant than

   ago('1 day')

Yours,
Laurenz Albe

Re: ago(interval) → timestamptz

Quan Zongliang <quanzongliang@yeah.net> — 2025-11-04T08:53:39Z


On 11/4/25 1:55 PM, Laurenz Albe wrote:
> On Mon, 2025-11-03 at 22:36 +0100, Florents Tselai wrote:
>> I realize this will get some "you can easily implement this yourself” pushback,
>> But I keep seeing and writing a lot of WHERE ts > now() - interval '1 day' expressions.
> 
> You can easily implement this yourself...
> Also, there already is something similar in the shape of 'yesterday'::timestamptz.
> Moreover, a good percentage of the users would instead need ago(interval) -> timestamp.
> 
> So I'd say that the added value is marginal, and I personally find
> 
>     current_timestamp - INTERVAL '1' DAY
> 
> more readable and more SQL standard compliant than
> 
>     ago('1 day')
> 
now() - interval '1 day' is merely an example. In fact, we could use any 
time. For example
   now() - interval '10 day 5 hours 21 minutes'
This is beyond the scope of what yesterday() can support.

Therefore, I think this patch can be accepted. Make the user's operation 
more convenient.

--
Quan Zongliang

> Yours,
> Laurenz Albe
>

Re: ago(interval) → timestamptz

Andreas Karlsson <andreas@proxel.se> — 2025-11-06T09:37:40Z

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.

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.

Andreas

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

Re: ago(interval) → timestamptz

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-06T14:54:23Z

On Thu, 2025-11-06 at 12:15 +0100, Florents Tselai wrote:
> > 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.

Lots of people model absolute time using "timestamp without time zone" with the
silent assumption that all such timestamps are UTC timestamps.  That would be
the additional piece of information.

But I admit that that makes date arithmetic less useful.

There is an equivalent for "now()": localtimestamp

Yours,
Laurenz Albe

Re: ago(interval) → timestamptz

Andreas Karlsson <andreas@proxel.se> — 2025-11-08T08:09:55Z

On 11/6/25 3:54 PM, Laurenz Albe wrote:
> On Thu, 2025-11-06 at 12:15 +0100, Florents Tselai wrote:
>>> 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.
> 
> Lots of people model absolute time using "timestamp without time zone" with the
> silent assumption that all such timestamps are UTC timestamps.  That would be
> the additional piece of information.
> 
> But I admit that that makes date arithmetic less useful.
> 
> There is an equivalent for "now()": localtimestamp

Oh, did not know of that function but using timestamp like this is 
dangerous and a bad idea. Let's not make life easier for people who 
misuse data types. The localtimestamp function should not have been 
introduced in the first place.

Andreas

Re: ago(interval) → timestamptz

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-08T17:30:44Z

On Sat, 2025-11-08 at 09:09 +0100, Andreas Karlsson wrote:
> On 11/6/25 3:54 PM, Laurenz Albe wrote:
> > On Thu, 2025-11-06 at 12:15 +0100, Florents Tselai wrote:
> > > > 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.
> > 
> > Lots of people model absolute time using "timestamp without time zone" with the
> > silent assumption that all such timestamps are UTC timestamps.  That would be
> > the additional piece of information.
> > 
> > But I admit that that makes date arithmetic less useful.
> > 
> > There is an equivalent for "now()": localtimestamp
> 
> Oh, did not know of that function but using timestamp like this is 
> dangerous and a bad idea.

I don't see the problem, but I guess that's getting severly off-topic.

Yours,
Laurenz Albe