Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVcCV-00A1KQ-5w for pgsql-general@arkaria.postgresql.org; Wed, 08 Jan 2025 19:58:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tVcCU-005onD-L4 for pgsql-general@arkaria.postgresql.org; Wed, 08 Jan 2025 19:58:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVcCU-005oi6-5e for pgsql-general@lists.postgresql.org; Wed, 08 Jan 2025 19:58:45 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVcCQ-000Zhl-0l for pgsql-general@postgresql.org; Wed, 08 Jan 2025 19:58:44 +0000 Received: by mail-ot1-x334.google.com with SMTP id 46e09a7af769-71fbb0d035dso78161a34.2 for ; Wed, 08 Jan 2025 11:58:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736366321; x=1736971121; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=7U7Qa0aWp2b/cZjp136vT2o/OwfXlra4KhBOmJeacPk=; b=XQc1BmJVYu/0rmwMFaGR+kkC43IEXR+pCSKISzwVuhBW1IX5LOLGre0TlFhU1Ol0ur emNb7XklwhubRtmBuAxZEEeOfN2yzdvntHMI9PsOooFxdSYcrP7x7tBoIv3HKCovOVOg WGbMb6H5UZ+InZndypqgypUUjWWh7ImlYEHPpz4/HNawyeQzdtZyA2vXP2qb1/krDJvv vzNuznuBRMCvn0PwCLd13cPEBO9G+49j0T/aKf5mEWMlj4LRhRvjTZbFn60fRqu3Miu9 Yl4V2lIq7ktkPG+faF3/EvwRH3CBxx0JjlP/scJEnCMPvZ5Vx+y+Pl4AO8ozed33g0oB K5gw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736366321; x=1736971121; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=7U7Qa0aWp2b/cZjp136vT2o/OwfXlra4KhBOmJeacPk=; b=cjVSM/IO8AmzyUUXEyV41FB8i7QKkSL1LiReGDLi5o77I5sgFU2+v/3wLyFybJWnk8 E33XmJWQw1UPtwp2V33r6+TiGdOcvL/NbYCN4zItajFUaiOi2d/iyFSmWOtvNyewL0WT CiSB1kE7MxwTNACOgiQUP8hwQg7sy88ajC6y6ZjC4qFamVp9OpyOHHe4he8MKU6pr1UD tbE/d3Bdx8bhFnk9TfwJWCyeNPQFYBGksxG5Fe/NJqf6SZwC1D9ICWsaVRNZ87l6VRuu z1RXtbWPOw1NVpmtMaAIDjTDglpx6ID/D5r6YGv+Slqg916J6wPN6/ETuGBbUvzpV5r0 GG9w== X-Gm-Message-State: AOJu0YxS3EwTpSejNxvYGSIHWRENjCgPWX51OwmhGfJZdMPM0F36YaZ8 AijETI1ECBEX5PtRSblJYnQVnAmOOuMKKyqflyPGjmu2TCGe0ovHSTFv+I0umEfxW0Ek0BaC2n+ Gh8cR+ua+szT2h5RC7gCPE5dqbO0vkw== X-Gm-Gg: ASbGncsxg+IZk5wgtzsb1ZF+c4LEPEBz7yv8sw5sAspGiQqV352bPqrYiEB9Qn+Tue3 IpVS/SPEZo70E4HWhGR9si2GiaUXuSs6OJk4lQ04= X-Google-Smtp-Source: AGHT+IGpjlZv2GypmBnaO3rFfESUvCmzflgVj8qKeTujsKJYvXLqMMTPtoA+O+h1Hkjrd/D88Xx36NueqL4F0DOh6e4= X-Received: by 2002:a05:6871:a08b:b0:297:28ec:9b26 with SMTP id 586e51a60fabf-2aa06945c60mr2040246fac.33.1736366319590; Wed, 08 Jan 2025 11:58:39 -0800 (PST) MIME-Version: 1.0 References: <8c663eab-6dbd-44f1-a8b5-d25a6a227ea9@aklaver.com> In-Reply-To: <8c663eab-6dbd-44f1-a8b5-d25a6a227ea9@aklaver.com> From: Ron Johnson Date: Wed, 8 Jan 2025 14:58:28 -0500 X-Gm-Features: AbW1kvbuBy7tsB2A1z3RtTed5cpKq2k4w68_lOAs2ryh2_mXovmLf01xnld671E Message-ID: Subject: Re: INTERVAL MINUTE TO SECOND didn't do what I thought it would do To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000ade6f6062b3749f1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ade6f6062b3749f1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 8, 2025 at 2:43=E2=80=AFPM Adrian Klaver 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 i= s > > 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 an= d > > 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-I= NTERVAL-INPUT > > "Also, field values =E2=80=9Cto the right=E2=80=9D of the least significa= nt 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=3D# 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_elapse= d > > ,cast(state as char(11)) as state > > from pg_stat_activity > > WHERE pid !=3D pg_backend_pid() > > and state !=3D '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 , and butter sauce. > > Don't boil me, I'm still alive. > > lobster! > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000ade6f6062b3749f1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 8, 2025 at 2:43=E2=80=AFPM Ad= rian Klaver <adrian.klaver@= 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&quo= t;=C2=A0column is
> to see how many minutes and seconds ago that the query started.=C2=A0 = (Why?=C2=A0
> Because that's useful to me, and the people I show the output to w= hen
> queries run for more than a few minutes.=C2=A0 We don't need=C2=A0= to see hours and
> days; just the total MMMMM:SS.mmm.)
>
> I'd hoped that ::INTERVAL MINUTE TO SECOND would=C2=A0do the trick= , but
> MINUTE TO SECOND seems to be ignored.

=C2=A0From here:

https://www.po= stgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

"Also, field values =E2=80=9Cto the right=E2=80=9D of the least signif= icant 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.<= /div>
=C2=A0
>= ;
> Is there cast=C2=A0magic 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.=C2=A0 Must decide if it's worth the time.
=
=C2=A0
> > TAPc=3D# select pid
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,datname as db, usename
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,to_char(query_start, 'YYYY-MM-DD= HH24:MI:SS.M= S <htt= p://SS.MS>')
> as =C2=A0qry_start
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,(now() - query_start)::INTERVAL MINU= TE TO SECOND as qry_elapsed
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,cast(state as char(11)) as state
> from pg_stat_activity
> WHERE pid !=3D pg_backend_pid()
> and state !=3D 'idle';
>=C2=A0 =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 =C2=A0
> qry_elapsed =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0state
> ---------+------+------------+-------------------------+--------------= -----------+-------------
>=C2=A0 =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=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=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=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
> (4 rows)
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

--
Adrian Klaver
adrian.klave= r@aklaver.com


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000ade6f6062b3749f1--