Thread: Formatting problems with negative intervals, TO_CHAR
This seems ... well, counter-intuitive at least:
(using Pg 7.4.1)
# select to_char('4 minutes'::interval -
'5 minutes 30 seconds'::interval, 'mi:ss');
to_char
--------- -1:-3
(1 row)
Why is the trailing zero lost? Why are there two minus signs?
I would expect '-1:30'.
Likewise,
# select to_char('4 minutes'::interval -
'4 minutes 30 seconds'::interval,
# 'mi:ss');
to_char
--------- 00:-3
(1 row)
I would expect '-00:30'.
I ended up fixing this with a very convoluted expression:
... case when last.time_count > prev.time_count then '+' else '-' end || to_char((abs(extract(epoch from
last.time_count)- extract(epoch from prev.time_count)) || 'seconds')::interval,'FMmi:ss.cc')
but I have to believe there is an easier way.
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jboes@qtm.net
Jeff Boes <jboes@qtm.net> writes:
> This seems ... well, counter-intuitive at least:
> (using Pg 7.4.1)
> # select to_char('4 minutes'::interval -
> '5 minutes 30 seconds'::interval, 'mi:ss');
> to_char
> ---------
> -1:-3
> (1 row)
> Why is the trailing zero lost? Why are there two minus signs?
> I would expect '-1:30'.
Yeah, me too. The underlying interval value seems right:
regression=# select '4 minutes'::interval - '5 minutes 30 seconds'::interval;?column?
------------00:01:30
(1 row)
so I think this is a to_char() bug. Possibly it's platform-dependent
--- the roundoff behavior for division with a negative input varies
across machines. However I do see the bug on HPUX 10.20 with CVS tip.
> Likewise,
> # select to_char('4 minutes'::interval -
> '4 minutes 30 seconds'::interval, 'mi:ss');
> to_char
> ---------
> 00:-3
> (1 row)
regards, tom lane
On Sun, Jun 06, 2004 at 06:40:56PM -0400, Tom Lane wrote:
> Jeff Boes <jboes@qtm.net> writes:
> > This seems ... well, counter-intuitive at least:
> > (using Pg 7.4.1)
>
> > # select to_char('4 minutes'::interval -
> > '5 minutes 30 seconds'::interval, 'mi:ss');
>
> > to_char
> > ---------
> > -1:-3
> > (1 row)
>
> > Why is the trailing zero lost? Why are there two minus signs?
>
> > I would expect '-1:30'.
>
> Yeah, me too. The underlying interval value seems right:
>
> regression=# select '4 minutes'::interval - '5 minutes 30 seconds'::interval;
> ?column?
> -----------
> -00:01:30
> (1 row)
>
> so I think this is a to_char() bug. Possibly it's platform-dependent
> --- the roundoff behavior for division with a negative input varies
> across machines. However I do see the bug on HPUX 10.20 with CVS tip.
Please, read PostgreSQL docs.
http://www.postgresql.org/docs/7.4/static/functions-formatting.html
Warning: to_char(interval, text) is deprecated and should not be used in newly-written code. It will be removed in the
nextversion.
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On Monday, 07 June 2004 09:52, Karel Zak wrote: > http://www.postgresql.org/docs/7.4/static/functions-formatting.html > > Warning: to_char(interval, text) is deprecated and should not be > used in newly-written code. It will be removed in the next version. This is news for me. Are there any suggestions what we should replace TO_CHAR with? For example, we were using TO_CHAR to print timestamp values in ISO format without milliseconds ("YYYY-MM-DD HH24:MI:SS" style), regardless of the current datestyle setting. I see only three solutions to do this without using TO_CHAR, and IMO none of them are very attractive: - do the formatting at the application level,- change the datestyle for this query only- an ugly construct using 6 EXTRACTfunctions I hope there is a more elegant way to solve this... Why was TO_CHAR deprecated anyway? It seemed to me like a very useful and flexible way to do date/time formatting. Tnx, stefan
On Mon, Jun 07, 2004 at 11:08:37AM +0200, Stefan Weiss wrote: > On Monday, 07 June 2004 09:52, Karel Zak wrote: > > http://www.postgresql.org/docs/7.4/static/functions-formatting.html > > > > Warning: to_char(interval, text) is deprecated and should not be ^^^^^^^^ > > used in newly-written code. It will be removed in the next version. > > This is news for me. Are there any suggestions what we should replace > TO_CHAR with? For example, we were using TO_CHAR to print timestamp > values in ISO format without milliseconds ("YYYY-MM-DD HH24:MI:SS" style), > regardless of the current datestyle setting. Ah.. ONLY the INTERVAL version of TO_CHAR() is deprecated! All othersversions for numbers, timestamp or date are supportednow and in futureversions too. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/