MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 8 Jan 2025 14:00:00 -0500
Message-ID: <CANzqJaAxzAO6ye9r3_T2HA_g1myZTEFH+z7uSzBf57Bh2ykWVg@mail.gmail.com>
Subject: INTERVAL MINUTE TO SECOND didn't do what I thought it would do
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a8ea09062b3678ae"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAxzAO6ye9r3_T2HA_g1myZTEFH%2Bz7uSzBf57Bh2ykWVg%40mail.gmail.com>
Precedence: bulk

--000000000000a8ea09062b3678ae
Content-Type: text/plain; charset="UTF-8"

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!

--000000000000a8ea09062b3678ae
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>PG 14.13</div><div><br></div><div>The goal of &quot;<=
span style=3D"font-family:monospace">(now() - query_start)::INTERVAL MINUTE=
 TO SECOND&quot;</span>=C2=A0column is to see how many minutes and seconds =
ago that the query started.=C2=A0 (Why?=C2=A0 Because that&#39;s useful to =
me, and the people I show the output to when queries run for more than a fe=
w minutes.=C2=A0 We don&#39;t need=C2=A0to see hours and days; just the tot=
al MMMMM:SS.mmm.)</div><div><br></div><div>I&#39;d hoped that ::INTERVAL MI=
NUTE TO SECOND would=C2=A0do the trick, but MINUTE TO SECOND seems to be ig=
nored.</div><div><br></div><div>Is there cast=C2=A0magic that does what I w=
ant?</div><div><br></div><div><font face=3D"monospace">TAPc=3D# select pid =
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0,datname as db, usename<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0,to_char(query_start, &#39;YYYY-MM-DD HH24:MI:<a href=3D"http://S=
S.MS">SS.MS</a>&#39;) as =C2=A0qry_start<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0,(no=
w() - query_start)::INTERVAL MINUTE TO SECOND as qry_elapsed<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0,cast(state as char(11)) as state<br>from pg_stat_activity=
 <br>WHERE pid !=3D pg_backend_pid()<br>and state !=3D &#39;idle&#39;;<br>=
=C2=A0 =C2=A0pid =C2=A0 | =C2=A0db =C2=A0| =C2=A0usename =C2=A0 | =C2=A0 =
=C2=A0 =C2=A0 =C2=A0qry_start =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =
=C2=A0 qry_elapsed =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0state =C2=A0 =C2=A0<=
br>---------+------+------------+-------------------------+----------------=
---------+-------------<br>=C2=A0 657996 | =C2=A0 =C2=A0 =C2=A0| replicator=
 | 2024-11-11 21:03:00.992 | 57 days 16:38:22.342449 | active =C2=A0 =C2=A0=
 <br>=C2=A04070070 | TAPc | TAP =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2025-01-08 13:=
41:23.202 | 00:00:00.132817 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | active =C2=A0 =C2=
=A0 <br>=C2=A04070069 | TAPc | TAP =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2025-01-08 =
13:41:23.140 | 00:00:00.194222 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | active =C2=A0 =
=C2=A0 <br>=C2=A04070065 | TAPc | TAP =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2025-01-=
08 13:41:23.238 | 00:00:00.096418 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | active =C2=
=A0 =C2=A0 <br>(4 rows)</font><br></div><div><br></div><div><br></div><span=
 class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"g=
mail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Death t=
o &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still =
alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div>=
</div>

--000000000000a8ea09062b3678ae--