If I have a select statement where I am formatting both a duration ( interval data type ) and timestamp ( timestamp with tz datatype ) I am getting an odd behavior. Note the extra characters following the seconds in the last_update column. ace_db=# select port_id, to_char(ppa.term_duration, 'HH24:MI:SS') as term_duration, to_char(ppa.last_update, 'YYYY-MM-DD HH:24:MI:SS' ) as last_update from log.peg_port_accumulator ppa; port_id | term_duration | last_update ---------+---------------+------------------------ 1 | 23:52:29 | 2014-10-17 11:24:44:58 11 | 00:00:00 | 2014-10-20 09:24:15:27 9 | 00:00:00 | 2014-10-20 09:24:16:53 6 | 01:41:19 | 2014-10-14 01:24:50:46 4 | 00:01:30 | 2014-10-14 01:24:52:11 2 | 00:00:05 | 2014-10-15 09:24:32:38 (6 rows) If I change the last_update format to 'YYYY-MM-DD HH:24:MI’ I will get the desired result ( incorrectly ) including the seconds ??? psql -V psql (PostgreSQL) 9.3.5 If I only select/format either the term_duration or last_update I get the proper results with a format string. ace_db=# \dS+ log.peg_port_accumulator Table "log.peg_port_accumulator" Column | Type | Modifiers | Storage | Stats target | Description ---------------+--------------------------+------------------------------+---------+--------------+------------- port_id | integer | not null | plain | | orig_count | integer | default 0 | plain | | orig_duration | interval | default '00:00:00'::interval | plain | | term_count | integer | default 0 | plain | | term_duration | interval | default '00:00:00'::interval | plain | | last_update | timestamp with time zone | default now() | plain | | Indexes: "peg_port_accumulator_pkey" PRIMARY KEY, btree (port_id) Has OIDs: no Seems like a bug ? Dave Day |
