Thread: to_char issue?
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? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes:
> 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?
Hmm ... it would be expected in the case of a timestamp, but seems
wrong when the input is interval. You do get sane behavior from
HH24. I guess the question is whether there is any use in different
behavior between HH12 and HH24 for intervals.
regards, tom lane
I wrote:
> Hmm ... it would be expected in the case of a timestamp, but seems
> wrong when the input is interval.
... although actually the documentation defines HH as "hour of day (01-12)"
so it seems that this is per docs; at least, if you wanted to have
intervals behave differently from timestamps then documenting it would
get much messier. If you consider an interval as being time since
midnight then it's not totally insane.
I also find by experiment that it's worked like this since
to_char(interval) was introduced in 7.2.
I'm kind of inclined to leave it as-is, unless someone can show that
Oracle behaves differently.
regards, tom lane
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. -- Bruce Momjian <bruce@momjian.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. + Index: src/backend/utils/adt/formatting.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.163 diff -c -c -r1.163 formatting.c *** src/backend/utils/adt/formatting.c 16 Feb 2010 21:18:01 -0000 1.163 --- src/backend/utils/adt/formatting.c 23 Feb 2010 01:39:21 -0000 *************** *** 2089,2096 **** case DCH_HH: case DCH_HH12: sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, ! tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 : ! tm->tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s); --- 2089,2096 ---- case DCH_HH: case DCH_HH12: sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, ! !is_interval && tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? ! 12 : tm->tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s);
Bruce Momjian 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. Oh, I should also mention that with the old code, 24 and 36 interval hours would also return '12'. :-( -- Bruce Momjian <bruce@momjian.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. +
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. -- Bruce Momjian <bruce@momjian.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. + Index: src/backend/utils/adt/formatting.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.164 diff -c -c -r1.164 formatting.c *** src/backend/utils/adt/formatting.c 23 Feb 2010 01:42:19 -0000 1.164 --- src/backend/utils/adt/formatting.c 23 Feb 2010 06:25:59 -0000 *************** *** 2089,2095 **** case DCH_HH: case DCH_HH12: sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, ! !is_interval && tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 : tm->tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); --- 2089,2096 ---- case DCH_HH: case DCH_HH12: sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, ! is_interval ? tm->tm_hour : ! tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 : tm->tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix));