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