pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormatting problems with negative intervals, TO_CHAR
5+ messages / 4 participants
[nested] [flat]
* Formatting problems with negative intervals, TO_CHAR
@ 2004-06-03 20:03 Jeff Boes <jboes@qtm.net>
2004-06-06 22:40 ` Re: Formatting problems with negative intervals, TO_CHAR Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Boes @ 2004-06-03 20:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Formatting problems with negative intervals, TO_CHAR
2004-06-03 20:03 Formatting problems with negative intervals, TO_CHAR Jeff Boes <jboes@qtm.net>
@ 2004-06-06 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 07:52 ` Re: Formatting problems with negative intervals, TO_CHAR Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-06-06 22:40 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Formatting problems with negative intervals, TO_CHAR
2004-06-03 20:03 Formatting problems with negative intervals, TO_CHAR Jeff Boes <jboes@qtm.net>
2004-06-06 22:40 ` Re: Formatting problems with negative intervals, TO_CHAR Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-07 07:52 ` Karel Zak <zakkr@zf.jcu.cz>
2004-06-07 09:08 ` Re: Formatting problems with negative intervals, TO_CHAR Stefan Weiss <spaceman@foo.at>
0 siblings, 1 reply; 5+ messages in thread
From: Karel Zak @ 2004-06-07 07:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 next version.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Formatting problems with negative intervals, TO_CHAR
2004-06-03 20:03 Formatting problems with negative intervals, TO_CHAR Jeff Boes <jboes@qtm.net>
2004-06-06 22:40 ` Re: Formatting problems with negative intervals, TO_CHAR Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 07:52 ` Re: Formatting problems with negative intervals, TO_CHAR Karel Zak <zakkr@zf.jcu.cz>
@ 2004-06-07 09:08 ` Stefan Weiss <spaceman@foo.at>
2004-06-07 09:46 ` Re: Formatting problems with negative intervals, TO_CHAR Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 5+ messages in thread
From: Stefan Weiss @ 2004-06-07 09:08 UTC (permalink / raw)
To: pgsql-sql
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 EXTRACT functions
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Formatting problems with negative intervals, TO_CHAR
2004-06-03 20:03 Formatting problems with negative intervals, TO_CHAR Jeff Boes <jboes@qtm.net>
2004-06-06 22:40 ` Re: Formatting problems with negative intervals, TO_CHAR Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 07:52 ` Re: Formatting problems with negative intervals, TO_CHAR Karel Zak <zakkr@zf.jcu.cz>
2004-06-07 09:08 ` Re: Formatting problems with negative intervals, TO_CHAR Stefan Weiss <spaceman@foo.at>
@ 2004-06-07 09:46 ` Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 0 replies; 5+ messages in thread
From: Karel Zak @ 2004-06-07 09:46 UTC (permalink / raw)
To: Stefan Weiss <spaceman@foo.at>; +Cc: pgsql-sql
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 others
versions for numbers, timestamp or date are supported now and in future
versions too.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-06-07 09:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-03 20:03 Formatting problems with negative intervals, TO_CHAR Jeff Boes <jboes@qtm.net>
2004-06-06 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 07:52 ` Karel Zak <zakkr@zf.jcu.cz>
2004-06-07 09:08 ` Stefan Weiss <spaceman@foo.at>
2004-06-07 09:46 ` Karel Zak <zakkr@zf.jcu.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox