public inbox for [email protected]
help / color / mirror / Atom feedINTERVAL MINUTE TO SECOND didn't do what I thought it would do
5+ messages / 2 participants
[nested] [flat]
* INTERVAL MINUTE TO SECOND didn't do what I thought it would do
@ 2025-01-08 19:00 Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-01-08 19:00 UTC (permalink / raw)
To: pgsql-general
PG 14.13
The goal of "(now() - query_start)::INTERVAL MINUTE TO SECOND" column is to
see how many minutes and seconds ago that the query started. (Why?
Because that's useful to me, and the people I show the output to when
queries run for more than a few minutes. We don't need to see hours and
days; just the total MMMMM:SS.mmm.)
I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, but MINUTE
TO SECOND seems to be ignored.
Is there cast magic that does what I want?
TAPc=# select pid
,datname as db, usename
,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS.MS') as qry_start
,(now() - query_start)::INTERVAL MINUTE TO SECOND as qry_elapsed
,cast(state as char(11)) as state
from pg_stat_activity
WHERE pid != pg_backend_pid()
and state != 'idle';
pid | db | usename | qry_start | qry_elapsed
| state
---------+------+------------+-------------------------+-------------------------+-------------
657996 | | replicator | 2024-11-11 21:03:00.992 | 57 days
16:38:22.342449 | active
4070070 | TAPc | TAP | 2025-01-08 13:41:23.202 | 00:00:00.132817
| active
4070069 | TAPc | TAP | 2025-01-08 13:41:23.140 | 00:00:00.194222
| active
4070065 | TAPc | TAP | 2025-01-08 13:41:23.238 | 00:00:00.096418
| active
(4 rows)
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INTERVAL MINUTE TO SECOND didn't do what I thought it would do
@ 2025-01-08 19:43 Adrian Klaver <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-01-08 19:43 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On 1/8/25 11:00 AM, Ron Johnson wrote:
> PG 14.13
>
> The goal of "(now() - query_start)::INTERVAL MINUTE TO SECOND" column is
> to see how many minutes and seconds ago that the query started. (Why?
> Because that's useful to me, and the people I show the output to when
> queries run for more than a few minutes. We don't need to see hours and
> days; just the total MMMMM:SS.mmm.)
>
> I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, but
> MINUTE TO SECOND seems to be ignored.
From here:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
"Also, field values “to the right” of the least significant field
allowed by the fields specification are silently discarded. For example,
writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
seconds field, but not the day field."
>
> Is there cast magic that does what I want?
The only way I can think of extract the epoch from the interval and pass
to a function that builds what you want.
>
> TAPc=# select pid
> ,datname as db, usename
> ,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS.MS <http://SS.MS>';)
> as qry_start
> ,(now() - query_start)::INTERVAL MINUTE TO SECOND as qry_elapsed
> ,cast(state as char(11)) as state
> from pg_stat_activity
> WHERE pid != pg_backend_pid()
> and state != 'idle';
> pid | db | usename | qry_start |
> qry_elapsed | state
> ---------+------+------------+-------------------------+-------------------------+-------------
> 657996 | | replicator | 2024-11-11 21:03:00.992 | 57 days
> 16:38:22.342449 | active
> 4070070 | TAPc | TAP | 2025-01-08 13:41:23.202 |
> 00:00:00.132817 | active
> 4070069 | TAPc | TAP | 2025-01-08 13:41:23.140 |
> 00:00:00.194222 | active
> 4070065 | TAPc | TAP | 2025-01-08 13:41:23.238 |
> 00:00:00.096418 | active
> (4 rows)
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INTERVAL MINUTE TO SECOND didn't do what I thought it would do
@ 2025-01-08 19:58 Ron Johnson <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-01-08 19:58 UTC (permalink / raw)
To: pgsql-general
On Wed, Jan 8, 2025 at 2:43 PM Adrian Klaver <[email protected]>
wrote:
>
>
> On 1/8/25 11:00 AM, Ron Johnson wrote:
> > PG 14.13
> >
> > The goal of "(now() - query_start)::INTERVAL MINUTE TO SECOND" column is
> > to see how many minutes and seconds ago that the query started. (Why?
> > Because that's useful to me, and the people I show the output to when
> > queries run for more than a few minutes. We don't need to see hours and
> > days; just the total MMMMM:SS.mmm.)
> >
> > I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, but
> > MINUTE TO SECOND seems to be ignored.
>
> From here:
>
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
>
> "Also, field values “to the right” of the least significant field
> allowed by the fields specification are silently discarded. For example,
> writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
> seconds field, but not the day field."
>
I read that, but it did not mention that the day values are retained.
> >
> > Is there cast magic that does what I want?
>
> The only way I can think of extract the epoch from the interval and pass
> to a function that builds what you want.
>
I was afraid of that. Must decide if it's worth the time.
> >
> > TAPc=# select pid
> > ,datname as db, usename
> > ,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS.MS <http://SS.MS>';)
>
> > as qry_start
> > ,(now() - query_start)::INTERVAL MINUTE TO SECOND as qry_elapsed
> > ,cast(state as char(11)) as state
> > from pg_stat_activity
> > WHERE pid != pg_backend_pid()
> > and state != 'idle';
> > pid | db | usename | qry_start |
> > qry_elapsed | state
> >
> ---------+------+------------+-------------------------+-------------------------+-------------
> > 657996 | | replicator | 2024-11-11 21:03:00.992 | 57 days
> > 16:38:22.342449 | active
> > 4070070 | TAPc | TAP | 2025-01-08 13:41:23.202 |
> > 00:00:00.132817 | active
> > 4070069 | TAPc | TAP | 2025-01-08 13:41:23.140 |
> > 00:00:00.194222 | active
> > 4070065 | TAPc | TAP | 2025-01-08 13:41:23.238 |
> > 00:00:00.096418 | active
> > (4 rows)
> >
> >
> > --
> > Death to <Redacted>, and butter sauce.
> > Don't boil me, I'm still alive.
> > <Redacted> lobster!
>
> --
> Adrian Klaver
> [email protected]
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INTERVAL MINUTE TO SECOND didn't do what I thought it would do
@ 2025-01-08 20:07 Adrian Klaver <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-01-08 20:07 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On 1/8/25 11:58 AM, Ron Johnson wrote:
> On Wed, Jan 8, 2025 at 2:43 PM Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
>
> > I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, but
> > MINUTE TO SECOND seems to be ignored.
>
> From here:
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT;
>
> "Also, field values “to the right” of the least significant field
> allowed by the fields specification are silently discarded. For
> example,
> writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
> seconds field, but not the day field."
>
>
> I read that, but it did not mention that the day values are retained.
I suggest reading the entire section(8.5.4. Interval Input) as well as
8.5.5. Interval Output.
>
> >
> > Is there cast magic that does what I want?
>
> The only way I can think of extract the epoch from the interval and
> pass
> to a function that builds what you want.
>
>
> I was afraid of that. Must decide if it's worth the time.
>
If you don't mind decimal minutes, a quick and dirty solution is:
select extract(epoch from (now() - '2025-01-07 14:15:32'::timestamptz))
/ 60;
1301.5244606333333333
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: INTERVAL MINUTE TO SECOND didn't do what I thought it would do
@ 2025-01-08 20:19 Ron Johnson <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-01-08 20:19 UTC (permalink / raw)
To: pgsql-general
On Wed, Jan 8, 2025 at 3:07 PM Adrian Klaver <[email protected]>
wrote:
>
>
> On 1/8/25 11:58 AM, Ron Johnson wrote:
> > On Wed, Jan 8, 2025 at 2:43 PM Adrian Klaver <[email protected]
> > <mailto:[email protected]>> wrote:
> >
>
> > > I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, but
> > > MINUTE TO SECOND seems to be ignored.
> >
> > From here:
> >
> >
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> <
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> >
> >
> > "Also, field values “to the right” of the least significant field
> > allowed by the fields specification are silently discarded. For
> > example,
> > writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping
> the
> > seconds field, but not the day field."
> >
> >
> > I read that, but it did not mention that the day values are retained.
>
> I suggest reading the entire section(8.5.4. Interval Input) as well as
> 8.5.5. Interval Output.
>
I did, but either missed something or did not interpret it correctly.
> >
> > >
> > > Is there cast magic that does what I want?
> >
> > The only way I can think of extract the epoch from the interval and
> > pass
> > to a function that builds what you want.
> >
> >
> > I was afraid of that. Must decide if it's worth the time.
> >
> If you don't mind decimal minutes, a quick and dirty solution is:
>
> select extract(epoch from (now() - '2025-01-07 14:15:32'::timestamptz))
> / 60;
>
> 1301.5244606333333333
>
That's what I currently do, using to_char() to add commas and display 3
decimal places.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-08 20:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-08 19:00 INTERVAL MINUTE TO SECOND didn't do what I thought it would do Ron Johnson <[email protected]>
2025-01-08 19:43 ` Adrian Klaver <[email protected]>
2025-01-08 19:58 ` Ron Johnson <[email protected]>
2025-01-08 20:07 ` Adrian Klaver <[email protected]>
2025-01-08 20:19 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox