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
