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 1tVcWr-00A4QB-0g for pgsql-general@arkaria.postgresql.org; Wed, 08 Jan 2025 20:19:49 +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 1tVcWp-0068yI-Vs for pgsql-general@arkaria.postgresql.org; Wed, 08 Jan 2025 20:19:47 +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 1tVcWp-0068xB-GW for pgsql-general@lists.postgresql.org; Wed, 08 Jan 2025 20:19:47 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVcWn-000Zt2-05 for pgsql-general@postgresql.org; Wed, 08 Jan 2025 20:19:46 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-71e19fee3b3so630544a34.0 for ; Wed, 08 Jan 2025 12:19:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736367584; x=1736972384; 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=2s+dP19Owj3vnTWWKifgGi2iR2ZglwP9SZUb56dnVVg=; b=g2IMvcb7GalZLvypKVlZ52x2c4tSMjPocdTJVacNU8352rog4MXTGFmADyhEkRVy8n 2Uz99fCWUDlXMaWSqdXmlJhtoolCnGs7JfORA8KAEb1LE5SJLZsGFr9ML5t/aM8Y0Iyh CbMKeFVOHWuBcyKI05Z5T41qWZ9RcYgEMmSxK0V2q9XP94+X990WyOASPyCz3BNyP9qN h9mDaM0n0id0DnycOVg3jTb8DEV/mcq1CpLYxo/y1nAu3jDqSEj7tR9pC14RoroQFO6a XkJ02jzrhoP8Fgc1WanBLkzNhx0U/SGRa1YJH63MVPI7/2yez9nAQRHzHvr3HcVdzo8h REHA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736367584; x=1736972384; 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=2s+dP19Owj3vnTWWKifgGi2iR2ZglwP9SZUb56dnVVg=; b=VBV9A/VjYapCga7BEFlCkOGlZx0VbwWFraOxzvaaE7GHScbFcowqs2mt8Z518ZncmM VMtWXvAgCqXln9R7dIso3qEUnSIEUHwxApjvqvLD8H0yAldxyJIxBg6IE5MfYVK43rVZ Q3G+kPLihIRwc7r5wLuAwgitsbgGDwqO7bShZ3Oz/7G4XLHvAcXaQuegQP12ZJ9eXXOm pD9zopEXdNcQKURxnpjfrZvUl1CfqUt/nscs8LM7VpS3ppRwIu3a87Lqwp8kwo8p1twS Rg0fXWRD2vumWdorvMkrDDTvos0S0Q/endfQY3i+OeSOZmbYdZLM1xuxWMCSL/9C0v+S V0bg== X-Gm-Message-State: AOJu0Yxv0FQ9ujzq49bL+hZlDc2Vfeesbrhnb++spV6HuR41omkt6XNq P+lBUcwmIxqY91Ngb4YN/+fOMaRvaElCp/dpxlwJoht1ZVAG3Uavk5SVJC8nd4SDlxu8J9Vtrpd 0oBKwBapPJRDKOxEwAYHVlnIXUEtOLQ== X-Gm-Gg: ASbGncvyGJCtBpnZ+WUPGVtEdoM+OEwO8xAoTNc7ITkmKcXovlpLwPNHGB3gEZ+wopr JDWMOnifu1Lmf/Rq9E9a9FPgkeppj50mpEp3NqbU= X-Google-Smtp-Source: AGHT+IHsTfzx5f+sVn3/n5RA4zbl2i9mNFwfeT6tUJi7Oa4P0E9LgUIDYLcG3HapvxTviSD6RHUe2RBOEJ6XgftVKj8= X-Received: by 2002:a05:6870:548c:b0:29d:caa2:f0ef with SMTP id 586e51a60fabf-2aae5aa916bmr416325fac.6.1736367584485; Wed, 08 Jan 2025 12:19:44 -0800 (PST) MIME-Version: 1.0 References: <8c663eab-6dbd-44f1-a8b5-d25a6a227ea9@aklaver.com> <071854d0-c524-436c-84d1-309aa096904d@aklaver.com> In-Reply-To: <071854d0-c524-436c-84d1-309aa096904d@aklaver.com> From: Ron Johnson Date: Wed, 8 Jan 2025 15:19:31 -0500 X-Gm-Features: AbW1kvZS7cx8vkFZ9Z-NLmKu9lW3PQJF63_RfJmkBcLGMhUPrCz7HZTb1Gkx7m8 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="00000000000012a91f062b3795b7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000012a91f062b3795b7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 8, 2025 at 3:07=E2=80=AFPM Adrian Klaver wrote: > > > On 1/8/25 11:58 AM, Ron Johnson wrote: > > On Wed, Jan 8, 2025 at 2:43=E2=80=AFPM Adrian Klaver > > wrote: > > > > > > I'd hoped that ::INTERVAL MINUTE TO SECOND would do the trick, b= ut > > > MINUTE TO SECOND seems to be ignored. > > > > From here: > > > > > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-I= NTERVAL-INPUT > < > 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 sig= nificant 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000012a91f062b3795b7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 8, 2025 at 3:07=E2=80=AFPM Ad= rian Klaver <adrian.klaver@= aklaver.com> wrote:


On 1/8/25 11:58 AM, Ron Johnson wrote:
> On Wed, Jan 8, 2025 at 2:43=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com=
> <mailto:adrian.klaver@aklaver.com>> wrote:
>

>=C2=A0 =C2=A0 =C2=A0 > I'd hoped that ::INTERVAL MINUTE TO SECON= D would=C2=A0do the trick, but
>=C2=A0 =C2=A0 =C2=A0 > MINUTE TO SECOND seems to be ignored.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0From here:
>
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/docs/current/datatype-datetime.html#= DATATYPE-INTERVAL-INPUT <https://www.postgresql.org/docs/current/datatype-datetime= .html#DATATYPE-INTERVAL-INPUT>
>
>=C2=A0 =C2=A0 =C2=A0"Also, field values =E2=80=9Cto the right=E2= =80=9D of the least significant field
>=C2=A0 =C2=A0 =C2=A0allowed by the fields specification are silently di= scarded. For
>=C2=A0 =C2=A0 =C2=A0example,
>=C2=A0 =C2=A0 =C2=A0writing INTERVAL '1 day 2:03:04' HOUR TO MI= NUTE results in dropping the
>=C2=A0 =C2=A0 =C2=A0seconds field, but not the day field."
>
>
> I read that, but it did not mention that the day values are retained.<= br>
I suggest reading the entire section(8.5.4. Interval Input) as well as
8.5.5. Interval Output.

I did, but eith= er missed something or did not interpret it correctly.
=C2=A0
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Is there cast=C2=A0magic that does what I wan= t?
>
>=C2=A0 =C2=A0 =C2=A0The only way I can think of extract the epoch from = the interval and
>=C2=A0 =C2=A0 =C2=A0pass
>=C2=A0 =C2=A0 =C2=A0to a function that builds what you want.
>
>
> I was afraid of that.=C2=A0 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'::timestamp= tz))
/ 60;

1301.5244606333333333

That's what I= currently do, using to_char() to add commas and display 3 decimal places.<= /div>

-- =
Death to &l= t;Redacted>, and butter sauce.
Don't boil me, I'm still aliv= e.
<Redacted> lobster!
--00000000000012a91f062b3795b7--