pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
6+ messages / 5 participants
[nested] [flat]
* BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-05-24 18:56 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2022-05-24 18:56 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: qubzen@gmail.com
The following bug has been logged on the website:
Bug reference: 17496
Logged by: Yankz
Email address: qubzen@gmail.com
PostgreSQL version: 13.7
Operating system: Mac Os
Description:
to_char function resets if interval exceeds 23 hours 59 minutes found at:
https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE
Tested for both 13 and 14 versions. Check here:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=31f5d8b7e8dbf855e1907e6e8f01d5cf
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-05-24 19:55 Jeff Janes <jeff.janes@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2022-05-24 19:55 UTC (permalink / raw)
To: qubzen@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Tue, May 24, 2022, 3:23 PM PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17496
> Logged by: Yankz
> Email address: qubzen@gmail.com
> PostgreSQL version: 13.7
> Operating system: Mac Os
> Description:
>
> to_char function resets if interval exceeds 23 hours 59 minutes found at:
>
> https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE
>
> Tested for both 13 and 14 versions. Check here:
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=31f5d8b7e8dbf855e1907e6e8f01d5cf
I don't see how this is a bug. You told it not to display the days or
higher units, so it doesn't.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-05-24 20:11 Nathan Bossart <nathandbossart@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2022-05-24 20:11 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: qubzen@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Tue, May 24, 2022 at 03:55:49PM -0400, Jeff Janes wrote:
> On Tue, May 24, 2022, 3:23 PM PG Bug reporting form <noreply@postgresql.org>
>> to_char function resets if interval exceeds 23 hours 59 minutes found at:
>>
>> https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE
>>
>> Tested for both 13 and 14 versions. Check here:
>>
>> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=31f5d8b7e8dbf855e1907e6e8f01d5cf
>
>
> I don't see how this is a bug. You told it not to display the days or
> higher units, so it doesn't.
The documentation for this function indicates HH24 can output hour values
higher than 23∴
<function>to_char(interval)</function> formats <literal>HH</literal> and
<literal>HH12</literal> as shown on a 12-hour clock, for example zero hours
and 36 hours both output as <literal>12</literal>, while <literal>HH24</literal>
outputs the full hour value, which can exceed 23 in
an <type>interval</type> value.
I see different results depending on how I define the interval:
postgres=# select to_char(INTERVAL '1 day', 'HH24:MI');
to_char
---------
00:00
(1 row)
postgres=# select to_char(INTERVAL '24h', 'HH24:MI');
to_char
---------
24:00
(1 row)
The example provided is more like the former (no hour value), because
"timestamp - timestamp" converts 24-hour intervals into days. In general,
I agree that this probably not a bug. You probably want to ask to_char()
to display the days as well..
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-07-14 21:57 Bruce Momjian <bruce@momjian.us>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2022-07-14 21:57 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; qubzen@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Tue, May 24, 2022 at 01:11:45PM -0700, Nathan Bossart wrote:
> The documentation for this function indicates HH24 can output hour values
> higher than 23∴
>
> <function>to_char(interval)</function> formats <literal>HH</literal> and
> <literal>HH12</literal> as shown on a 12-hour clock, for example zero hours
> and 36 hours both output as <literal>12</literal>, while <literal>HH24</literal>
> outputs the full hour value, which can exceed 23 in
> an <type>interval</type> value.
>
> I see different results depending on how I define the interval:
>
> postgres=# select to_char(INTERVAL '1 day', 'HH24:MI');
> to_char
> ---------
> 00:00
> (1 row)
>
> postgres=# select to_char(INTERVAL '24h', 'HH24:MI');
> to_char
> ---------
> 24:00
> (1 row)
>
> The example provided is more like the former (no hour value), because
> "timestamp - timestamp" converts 24-hour intervals into days. In general,
> I agree that this probably not a bug. You probably want to ask to_char()
> to display the days as well..
Well, if we did that then this would be odd:
SELECT to_char(INTERVAL '2 day', 'DD HH24:MI');
to_char
----------
02 00:00
or this:
SELECT to_char(INTERVAL '2 day 4 hours', 'DD HH24:MI');
to_char
----------
02 04:00
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-07-15 07:58 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kyotaro Horiguchi @ 2022-07-15 07:58 UTC (permalink / raw)
To: bruce@momjian.us; +Cc: nathandbossart@gmail.com; jeff.janes@gmail.com; qubzen@gmail.com; pgsql-bugs@lists.postgresql.org
At Thu, 14 Jul 2022 17:57:32 -0400, Bruce Momjian <bruce@momjian.us> wrote in
> On Tue, May 24, 2022 at 01:11:45PM -0700, Nathan Bossart wrote:
> > The documentation for this function indicates HH24 can output hour values
> > higher than 23$B!h(B
> >
> > <function>to_char(interval)</function> formats <literal>HH</literal> and
> > <literal>HH12</literal> as shown on a 12-hour clock, for example zero hours
> > and 36 hours both output as <literal>12</literal>, while <literal>HH24</literal>
> > outputs the full hour value, which can exceed 23 in
> > an <type>interval</type> value.
> >
> > I see different results depending on how I define the interval:
> >
> > postgres=# select to_char(INTERVAL '1 day', 'HH24:MI');
> > to_char
> > ---------
> > 00:00
> > (1 row)
> >
> > postgres=# select to_char(INTERVAL '24h', 'HH24:MI');
> > to_char
> > ---------
> > 24:00
> > (1 row)
> >
> > The example provided is more like the former (no hour value), because
> > "timestamp - timestamp" converts 24-hour intervals into days. In general,
> > I agree that this probably not a bug. You probably want to ask to_char()
> > to display the days as well..
On the other hand, "interval + interval" doesn't convert hours into
days. I expected the math to do normalization.
select INTERVAL '1 day 15hour' + interval '2 day 15 hour';
?column?
-----------------
3 days 30:00:00
Is there any means to control over normalization?
> select normalize_interval('3 days 30:00:00'::interval, 'HH24:MI')
> 102:00
> select normalize_interval('3 days 30:00:00'::interval, 'DD HH24:MI')
> 4 04:00
> select normalize_interval('3 days 30:00:00'::interval, 'HH12:MI')
> ERROR: hours out of range
> Well, if we did that then this would be odd:
>
> SELECT to_char(INTERVAL '2 day', 'DD HH24:MI');
> to_char
> ----------
> 02 00:00
>
> or this:
>
> SELECT to_char(INTERVAL '2 day 4 hours', 'DD HH24:MI');
> to_char
> ----------
> 02 04:00
Mmm. I don't see this is odd...
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes
@ 2022-07-18 21:21 Bruce Momjian <bruce@momjian.us>
parent: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2022-07-18 21:21 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: nathandbossart@gmail.com; jeff.janes@gmail.com; qubzen@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Jul 15, 2022 at 04:58:35PM +0900, Kyotaro Horiguchi wrote:
> At Thu, 14 Jul 2022 17:57:32 -0400, Bruce Momjian <bruce@momjian.us> wrote in
> > On Tue, May 24, 2022 at 01:11:45PM -0700, Nathan Bossart wrote:
> > > The example provided is more like the former (no hour value), because
> > > "timestamp - timestamp" converts 24-hour intervals into days. In general,
> > > I agree that this probably not a bug. You probably want to ask to_char()
> > > to display the days as well..
>
> On the other hand, "interval + interval" doesn't convert hours into
> days. I expected the math to do normalization.
>
> select INTERVAL '1 day 15hour' + interval '2 day 15 hour';
> ?column?
> -----------------
> 3 days 30:00:00
>
> Is there any means to control over normalization?
Sure, justify_hours():
SELECT INTERVAL '1 day 15hour' + interval '2 day 15 hour';
?column?
-----------------
3 days 30:00:00
SELECT justify_hours(INTERVAL '1 day 15hour' + interval '2 day 15
hour');
justify_hours
-----------------
4 days 06:00:00
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-07-18 21:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-24 18:56 BUG #17496: to_char function resets if interval exceeds 23 hours 59 minutes PG Bug reporting form <noreply@postgresql.org>
2022-05-24 19:55 ` Jeff Janes <jeff.janes@gmail.com>
2022-05-24 20:11 ` Nathan Bossart <nathandbossart@gmail.com>
2022-07-14 21:57 ` Bruce Momjian <bruce@momjian.us>
2022-07-15 07:58 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2022-07-18 21:21 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox