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

From Day, David
Subject problem in to_char( ) ?
Date
Msg-id 401084E5E73F4241A44F3C9E6FD79428010D8EB20C@exch-01
Whole thread Raw
Responses Re: problem in to_char( ) ?
List pgsql-general

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

 

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Yosemite (OSX 10.0) problems with Postgresql
Next
From: David G Johnston
Date:
Subject: Re: problem in to_char( ) ?