Re: Timestamp/Interval proposals: Part 2

On Tue, 2002-06-11 at 09:34, Karel Zak wrote:
> On Mon, Jun 10, 2002 at 07:18:44PM +0200, Hannu Krosing wrote:
> 
>  OK, I add to_interval() to may TODO (but it's unsure for 7.3).
> 
> > hannu=# select to_char('33s 15h 10m 5months'::interval, 'YYYY.MM.DD
> > HH24:MI:SS');
> >        to_char       
> > ---------------------
> >  0000.05.00 15:10:33
> > (1 row)

I have not checked the SQL9x standards, but it seems from reading the
following links that Interval in Oracle and MimerSQL is actually 2
distinct types (YEAR-MONTH interval and DAY-HOUR-MINUTE-SECOND interval)
which can't be mixed (it is impossible to know if 1 "month" is 28, 29,
30 or 31 days

http://otn.oracle.com/products/rdb7/htdocs/y2000.htm

http://developer.mimer.com/documentation/Mimer_SQL_Reference_Manual/Syntax_Rules4.html#1113356

>  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')


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