Обсуждение: 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
Вложения
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
> 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.
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.
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