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
float to_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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz