Re: problem in to_char( ) ? - Mailing list pgsql-general

Day, David wrote
> 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

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.