Re: [BUGS] BUG #2056: to_char no long takes time as input? - Mailing list pgsql-patches

Here is a patch that allows HH and HH12 to be used with interval, and
hence time.  I added documentation to warn users that using those with
intervals are mapped to single-day values.

I will soon apply this to HEAD and 8.1.X.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I see your issue with HH/HH24, but I wanted this to work:
>
> >     test=> select to_char('14 hours'::interval, 'HH');
> >      to_char
> >     ---------
> >      14
> >     (1 row)
>
> > With the HH/HH24 change that is going to return 2.  Do interval folks
> > know they would have to use HH24 for intervals?
>
> Dunno if they know it, but they always had to do it that way before 8.1,
> so it's not a change to require it.  I get this in everything back to
> 7.2:
>
> regression=# select to_char('14 hours'::interval, 'HH');
>  to_char
> ---------
>  02
> (1 row)
>
> regression=# select to_char('14 hours'::interval, 'HH24');
>  to_char
> ---------
>  14
> (1 row)
>
> and I don't see anything especially wrong with that behavior, as long as
> it's documented.
>
> > Should we subtract 12 only if the time is < 24.  That also seems
> > strange.  Also, a zero hour interval to HH would return 12, not 0.
>
> Offhand I'd vote for making the HH code use a "mod 12" calculation,
> and making AM/PM depend on the value "mod 24".  This gives at least a
> slightly sane behavior for intervals > 24 hours.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.296
diff -c -c -r1.296 func.sgml
*** doc/src/sgml/func.sgml    28 Nov 2005 23:18:48 -0000    1.296
--- doc/src/sgml/func.sgml    2 Dec 2005 03:51:45 -0000
***************
*** 4749,4754 ****
--- 4749,4762 ----
          <function>extract</function> function.
        </para>
       </listitem>
+
+      <listitem>
+       <para><function>to_char(interval)</function> formats <literal>HH</> and
+         <literal>HH12</> to hours in a single day, while <literal>HH24</>
+         can output hours exceeding a single day, e.g. >24.
+       </para>
+      </listitem>
+
      </itemizedlist>
     </para>

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.102
diff -c -c -r1.102 formatting.c
*** src/backend/utils/adt/formatting.c    22 Nov 2005 18:17:22 -0000    1.102
--- src/backend/utils/adt/formatting.c    2 Dec 2005 03:51:51 -0000
***************
*** 1722,1732 ****
      {
          case DCH_A_M:
          case DCH_P_M:
-             INVALID_FOR_INTERVAL;
              if (is_to_char)
              {
!                 strcpy(inout, ((tm->tm_hour > 11
!                        && tm->tm_hour < HOURS_PER_DAY) ? P_M_STR : A_M_STR));
                  return strlen(p_inout);
              }
              else
--- 1722,1731 ----
      {
          case DCH_A_M:
          case DCH_P_M:
              if (is_to_char)
              {
!                 strcpy(inout, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
!                        ? P_M_STR : A_M_STR);
                  return strlen(p_inout);
              }
              else
***************
*** 1742,1752 ****
              break;
          case DCH_AM:
          case DCH_PM:
-             INVALID_FOR_INTERVAL;
              if (is_to_char)
              {
!                 strcpy(inout, ((tm->tm_hour > 11
!                          && tm->tm_hour < HOURS_PER_DAY) ? PM_STR : AM_STR));
                  return strlen(p_inout);
              }
              else
--- 1741,1750 ----
              break;
          case DCH_AM:
          case DCH_PM:
              if (is_to_char)
              {
!                 strcpy(inout, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
!                        ? PM_STR : AM_STR);
                  return strlen(p_inout);
              }
              else
***************
*** 1762,1772 ****
              break;
          case DCH_a_m:
          case DCH_p_m:
-             INVALID_FOR_INTERVAL;
              if (is_to_char)
              {
!                 strcpy(inout, ((tm->tm_hour > 11
!                        && tm->tm_hour < HOURS_PER_DAY) ? p_m_STR : a_m_STR));
                  return strlen(p_inout);
              }
              else
--- 1760,1769 ----
              break;
          case DCH_a_m:
          case DCH_p_m:
              if (is_to_char)
              {
!                 strcpy(inout, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
!                        ? p_m_STR : a_m_STR);
                  return strlen(p_inout);
              }
              else
***************
*** 1782,1792 ****
              break;
          case DCH_am:
          case DCH_pm:
-             INVALID_FOR_INTERVAL;
              if (is_to_char)
              {
!                 strcpy(inout, ((tm->tm_hour > 11
!                          && tm->tm_hour < HOURS_PER_DAY) ? pm_STR : am_STR));
                  return strlen(p_inout);
              }
              else
--- 1779,1788 ----
              break;
          case DCH_am:
          case DCH_pm:
              if (is_to_char)
              {
!                 strcpy(inout, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
!                        ? pm_STR : am_STR);
                  return strlen(p_inout);
              }
              else
***************
*** 1804,1815 ****
          case DCH_HH12:
              if (is_to_char)
              {
!                 if (is_interval)
!                     sprintf(inout, "%0*d", S_FM(suf) ? 0 : 2, tm->tm_hour);
!                 else
!                     sprintf(inout, "%0*d", S_FM(suf) ? 0 : 2,
!                             tm->tm_hour == 0 ? 12 :
!                           tm->tm_hour < 13 ? tm->tm_hour : tm->tm_hour - 12);
                  if (S_THth(suf))
                      str_numth(p_inout, inout, 0);
                  return strlen(p_inout);
--- 1800,1808 ----
          case DCH_HH12:
              if (is_to_char)
              {
!                 sprintf(inout, "%0*d", S_FM(suf) ? 0 : 2,
!                         tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 :
!                         tm->tm_hour % (HOURS_PER_DAY / 2));
                  if (S_THth(suf))
                      str_numth(p_inout, inout, 0);
                  return strlen(p_inout);
***************
*** 2312,2318 ****
              }
              break;
          case DCH_D:
-             INVALID_FOR_INTERVAL;
              if (is_to_char)
              {
                  sprintf(inout, "%d", tm->tm_wday + 1);
--- 2305,2310 ----