to_char issue?

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. +

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. +