Formatting problems with negative intervals, TO_CHAR - Mailing list 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