Re: Timestamp/Interval proposals: Part 2

On Tue, Jun 11, 2002 at 06:22:55AM -0700, Thomas Lockhart wrote:
> > > fduch=> SELECT to_char('100days'::interval, 'YYYY-MM-DD HH24:MI:SS');
> > > ---------------------
> > >  0000-00-10 00:00:00
> >  I already said it. The to_char() is 'tm' struct interpreter and use
> >  standard internal PG routines for interval to 'tm' conversion. We can
> >  talk about why 100days is converted to '10' days and months aren't
> >  used. I agree this example seems strange. Thomas?
> 
> Not sure why 100 is becoming 10, except that the formatting string is
> specifying a field width of two characters (right?). And for intervals,
Oops. Yes, you are right it's %02d. I forgot it. Sorry :-)

> years and months are not interchangable with days so values do not
> overflow from days to months fields.
> 
> I played around with to_char(interval,text) but don't understand the
> behavior either.
OK. And what is wanted behavior?
 DD = day ## = error
1) '30h 10m 15s' 'HH MI SS'     ---> '06 10 15'   '30h 10m 15s' 'HH MI SS DD'  ---> '06 10 15 1'
2) '30h 10m 15s' 'HH MI SS'    ---> '30 10 15'   '30h 10m 15s' 'HH MI SS DD' ---> '30 10 15 ##'
3) '30h 10m 15s' 'HH MI SS'    ---> '30 10 15'   '30h 10m 15s' 'HH MI SS DD'  ---> '06 10 15 1'
4) use both 1) and 2) but with different marks like   'HH' and '#HH' (or other special prefix)
5) '2week' 'DD'    --->  '14'6) '2week' 'HH'    --->  '00'
7) '2week' 'HH'    --->  '336'
8) '2week' 'DD HH' --->  '14 00'
9) ???
I unsure what is best, Please, mark right outputs or write examples.
-- for all is probably right idea use '####' in output if input is not possible convert to wanted format (like current
floatto_char() behavior).
 
BTW:

test=# select date_part('hour', '30h 10m 15s'::interval);date_part 
-----------        6
test=# select date_part('day', '30h 10m 15s'::interval);date_part 
-----------        1

   Karel        
-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz