Re: Timestamp/Interval proposals: Part 2

On Tue, 2002-06-11 at 11:21, Karel Zak wrote:
> On Tue, Jun 11, 2002 at 11:16:13AM +0200, Hannu Krosing wrote:
> > On Tue, 2002-06-11 at 09:34, Karel Zak wrote:
>
> > > I think, we can keep this behaviour for to_char(), the good thing
> > > is that you can formatting interval to strings that seems like
> > > standard time (15:10:33), etc.
> >
> > But interval _is_ _not_ point-in-time, it is a time_span_ .
> >
> > It can be either good if it gives the results you want or bad if it does
> > give wrong results like returning 03:10:33 for the above
> >
> > I would suggest that a separate to_char function would be written that
> > would be _specific_to_interval_ datatype - so wheb i do
> >
> > to_char('33s 15h 10m'::interval, 'SS') I will get the actual length of
> >
> > interval in seconds, 15*3600+10*60+33 = 54633s and not just the seconds part (33)
> >
> > whereas to_char('33s 15h 10m'::interval, 'MI SS') would give
> >
> > 15*60+10=910 min 33 sec ('910 33')
>
> Well, If the to_char() for interval will output result that you want,
> how can I output '15:10:33'?
>
> For this I want two direffent function or anothers format marks for
> to_char() like
>
> to_char('33s 15h 10m'::interval, '#MI #SS');
> ---
> '910 33'

and it is probably easyer to implement too - no need to first collect
all possible format chars.

> but for "standard" marks (that now works like docs describe :-) will output
> MI in 0..59 range.
>
> to_char('33s 15h 10m'::interval, 'MI:SS');
> ---
> '10:33'
>
> IMHO it's acceptable. I don't want close the way for output formatting
> in "standard" date/time ranges. We can support _both_ ways. Or not?

perhaps we should do as to_char does for floats -- return ### if
argument cant be shown with given format ?

hannu=# select to_char(1000.0,'0000D00') as good,
hannu-# to_char(1000.0, '000D00') as bad;
good | bad
----------+---------
1000.00 | ###.##
(1 row)

no need to change current documented behaviour without good reason

> Thomas, you are quiet? :-)
>
> Karel
>
>
> PS. the PostgreSQL converting intervals to "standard" format too:
>
> test=# select '33h 15m'::interval - '10h 2m 3s'::interval ;
> ?column?
> ----------
> 23:12:57
> (1 row)
>
> test=# select '45h 15m'::interval - '10h 2m 3s'::interval ;
> ?column?
> ----------------
> 1 day 11:12:57
>
> (hmm.. I unsure if this is really released 7.2, I maybe have
> some pre-7.2 version now. Is this 7.2 behaviuor?)

Yes.

And this is still an interval, not a timestamp:

hannu=# select '4500h 15m'::interval - '10h 2m 3s'::interval ;
?column?
-------------------
187 days 02:12:57
(1 row)

----------------------------------
Hannu