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.
"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!
