problem in to_char( ) ?

[Day, David]

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

[David G Johnston]

Post by Day, David
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 | |
"peg_port_accumulator_pkey" PRIMARY KEY, btree (port_id)
Has OIDs: no
Seems like a bug ?
Dave Day

You have an extra ":" in your format...the "24" is being seen as a literal
because of the ":" between it and the HH. See your first format expression.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/problem-in-to-char-tp5823690p5823691.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.