Re: Timestamp/Interval proposals: Part 2 - Mailing list pgsql-hackers

On Mon, 2002-06-10 at 15:43, Karel Zak wrote:
> On Mon, Jun 10, 2002 at 04:26:47PM +0200, Hannu Krosing wrote:
>  
> > >  to_char() convert interval to 'tm' and make output like this struct,
> > 
> > My point is that to_char-ing intervals by converting them to dates is
> > non-intuitive.
> > 
> > It is really confusing to say that an interval of 5 months = "May"
> > and 15months == "1 March" ;(
> > 
> > >  I don't know what other is possible do with it.
> > 
> > perhaps show them with the precision specified and keep data for bigger
> > units in biggest specified unit.
> > 
> > to_char('2years 1min 4sec'::interval, 'MM SS'); ==> '24mon 64sec'
> > to_char('2years 1min 4sec'::interval, 'MM MI SS'); ==> '24mon 1min 4sec'
> > 
> 
>  Hmmm, but it's really out of to_char(). For example 'MM' is defined
>  as number in range 1..12.
>  
>  The to_char() convert date/time data to string and not to better formatted 
>  interval. The right name for your request is to_interval(). 

if there were a to_interval() then it should convert char data to
interval, like to_date(), to_number() and to_timestamp() do

actually we currently have to_char(x,t) functions for formatting the
following input types, where the second arg is always the format - and
they do take different format strings for different types (i.e. we dont
convert int or double to timestamp and then format that)

to_char | bigint, text
to_char | double precision, text
to_char | integer, text
to_char | interval, text
to_char | numeric, text
to_char | real, text
to_char | timestamp with time zone, text
to_char | timestamp without time zone, text

if our current implementation just converts interval to date it is
surely wrong, at least because the year will be 0000 which does not
exist (AFAIK, the year before 0001 was -0001)

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)

IMHO there should be INTERVAL-specific format characters - calling
5-month period "a May" is stupid (calling 1-month period "a January" is
even stupider :)

If folks want to convert interval to datetime they can always do it by
adding an interval to some base date - doing it automatically by adding
it to non-existing base date 000-00-00 will confuse people 

and it is not supported in "plain" postgresql

hannu=# select ('33s 15h 10m 5months'::interval::timestamp);
ERROR:  Cannot cast type 'interval' to 'timestamp with time zone'

> TODO?

having strictly defined to_interval would be nice, but I think this
would be _another_ todo :)

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