Re: Timestamp/Interval proposals: Part 2

Karel, Hannu,

To be perfectly honest, I was looking at my 7.1 documentation (courtesy
of DOSSIER) and hadn't realized that 7.2's implementation had got as
far as a function.  I had tried to_char(interval) on 7.2.1, received
what looked like gibberish in return, and assumed that it was
unimplemented.

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

Can we put THAT on the to-do list?  I find it highly inconsistent that
the function for creating intervals is "interval".  Currently, I deal
with it by creating my own to_interval function in template1.  

> 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)
<snip>
> 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 :)

I wholeheartedly agree with Hannu, here.   Might I suggest:

M# - Nummber of Months - abbr (Interval)
MM# - Number of Months (interval)
Y# - Number of years - abbr (Interval)
YY# - Number of years (Interval)
D# - Number of Days (interval)
W# - Number of weeks -abbr (interval)
WW# - number of weeks (interval)
HH# - Number of hours (interval)
MI# - Number of minutes (interval)
SS# - Number of seconds (interval)

Thus allowing:

hannu=# select to_char('33s 15h 10m 5months'::interval, 'M# D# HH# MI#
SS#');       to_char 
---------------------5 mon 0 days 15 hrs 10 min 33 sec 

or:

hannu=# select to_char('33s 15h 10m 5months'::interval, 'MM# D# HH# MI#
SS#');       to_char 
---------------------5 months 0 days 15 hrs 10 min 33 sec 

This needs more polishing, of course, but you can see where I'm going
with it.

-Josh



______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco