Skip site navigation (1) Skip section navigation (2)

Re: to_char("HH12") with intervals

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Dave Page <dpage(at)pgadmin(dot)org>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: to_char("HH12") with intervals
Date: 2010-02-23 16:16:12
Message-ID: 201002231616.o1NGGCG23974@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian wrote:
> bruce wrote:
> > bruce wrote:
> > > Dave Page wrote:
> > > > This was posted as a documentation comment:
> > > > 
> > > > to_char(interval '0d 0h 12m 44s', 'DD HH MI SS');
> > > > with HH and HH12 will return 12 instead of 0.
> > > > 
> > > > Testing on 8.4.1, it does seem to be the case that you get "00 12 12
> > > > 44". Seems bogus to me, but am I and the OP missing something?
> > > 
> > > Fixed with the attached patch.  I think HH and HH24 should be the same
> > > for intervals.  It is hard to explain why zero hours should show as
> > > '12' for intervals.
> > 
> > Oops, I needed a second patch to fix hours > 12 for intervals.  Patch
> > attached and applied.  It will now report the full hours of the
> > interval.
> 
> We currently have this in our documentation:
> 
>         <function>to_char(interval)</function> formats <literal>HH</> and
>         <literal>HH12</> as hours in a single day, while <literal>HH24</>
>         can output hours exceeding a single day, e.g., &gt;24.
> 
> This seems pretty confusing because HH/HH12 formats as hours in a single
> 1/2 day, 12 hours, and it really does wall-clock time, zero hours is 12,
> and for intervals it does the right thing now and prints the interval
> hours.
> 
> We also have these range definitions:
> 
>        <row>
>         <entry><literal>HH12</literal></entry>
>         <entry>hour of day (01-12)</entry>
>        </row>
>        <row>
>         <entry><literal>HH24</literal></entry>
>         <entry>hour of day (00-23)</entry>
>        </row>
> 
> HH24 could always be > 24 for intervals, and now HH12 can too for
> intervals.
> 
> What should be changed here?

I have rethought this and now realize the original code was fine, but
the documentation was very unclear.  I have reverted the patch and added
a C command and documentation updates with examples; attached.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

Attachment: /rtmp/diff
Description: text/x-diff (2.1 KB)

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2010-02-23 16:18:10
Subject: Re: pretty print viewdefs
Previous:From: Tom LaneDate: 2010-02-23 16:13:06
Subject: Re: [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group