On Wed, Jan 8, 2025 at 2:43 PM Adrian Klaver <adrian.kla...@aklaver.com> 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 > adrian.kla...@aklaver.com > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
