ago(interval) → timestamptz
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
Attachments:
v1-0001-Add-ago-interval-function-to-subtract-interval-fr.patchapplication/octet-stream; name=v1-0001-Add-ago-interval-function-to-subtract-interval-fr.patchDownload
From f28d87abf7f16ca3aa21eb42f0f8d05d69868dbe Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Mon, 3 Nov 2025 22:20:18 +0100
Subject: [PATCH v1] Add ago(interval) function to subtract interval from
current timestamp
---
doc/src/sgml/func/func-datetime.sgml | 16 ++++++++++++++++
src/backend/catalog/system_functions.sql | 6 ++++++
src/include/catalog/pg_proc.dat | 3 +++
3 files changed, 25 insertions(+)
diff --git a/doc/src/sgml/func/func-datetime.sgml b/doc/src/sgml/func/func-datetime.sgml
index 8cd7150b0d3..c0b9830b638 100644
--- a/doc/src/sgml/func/func-datetime.sgml
+++ b/doc/src/sgml/func/func-datetime.sgml
@@ -359,6 +359,22 @@
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <function>ago</function> ( <type>interval</type> )
+ <returnvalue>timestamptz</returnvalue>
+ </para>
+ <para>
+ Returns the current timestamp minus the specified interval.
+ </para>
+ <para>
+ <literal>ago(interval '1 hour')</literal>
+ <returnvalue>2025-11-03 14:42:31.123456+01</returnvalue>
+ </para>
+ </entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..c035d509608 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -127,6 +127,12 @@ CREATE OR REPLACE FUNCTION age(timestamp)
STABLE PARALLEL SAFE STRICT COST 1
RETURN age(cast(current_date as timestamp), $1);
+CREATE OR REPLACE FUNCTION ago(interval)
+ RETURNS timestamptz
+ LANGUAGE sql
+ STABLE PARALLEL SAFE STRICT COST 1
+RETURN current_timestamp - $1;
+
CREATE OR REPLACE FUNCTION date_part(text, date)
RETURNS double precision
LANGUAGE sql
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..58df79bf163 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6481,6 +6481,9 @@
proname => 'age', prolang => 'sql', provolatile => 's',
prorettype => 'interval', proargtypes => 'timestamp',
prosrc => 'see system_functions.sql' },
+{ oid => '2173', descr => 'current timestamp minus the specified interval', proname => 'ago', prolang => 'sql',
+ provolatile => 's', prorettype => 'timestamptz', proargtypes => 'interval',
+ prosrc => 'see system_functions.sql' },
{ oid => '2069', descr => 'adjust timestamp to new time zone',
proname => 'timezone', prorettype => 'timestamptz',
--
2.49.0
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
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
Show quoted text
Yours,
Laurenz Albe
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
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
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
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
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