Problem with date formatting and FM

From: "Kistler, Winnie C(dot)" <kistlerwc(at)ornl(dot)gov>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Problem with date formatting and FM
Date: 2015-03-30 13:50:09
Message-ID: D13ECCCE.18A20%kistlerwc@ornl.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I was trying to use the data type formatting function, "to_char(timestamp, text)" to format a date, and I seem to be running into a bug, unless there is some other explanation that I'm missing. I tried looking it up in the PostgreSQL Todo list to see if it was reported as a bug, but I don't see it listed.

http://www.postgresql.org/docs/current/static/functions-formatting.html
In the documentation for PostgreSQL 9.4 under "9.8 Data type Formatting Functions", and after "Table 9-23. Template Pattern Modifiers for Date/Time Formatting", it states:
"Usage notes for date/time formatting:

* FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. In PostgreSQL, FM modifies only the next specification, while in Oracle FM affects all subsequent specifications, and repeated FM modifiers toggle fill mode on and off."

Specifically, I'm looking at "In PostgreSQL, FM modifies only the next specification". This does not seem to be the case for dates that are formatted to look like "Mon DD YYYY HH12:MIAM". I am assuming that the month, date, year, and hours are different specifications and not part of the same specification string per the example in "Table 9-26. to_char Examples".

Table 9-26. to_char Examples

Expression Result
to_char(current_timestamp, 'Day, DD HH12:MI:SS') 'Tuesday , 06 05:39:18'
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS') 'Tuesday, 6 05:39:18'
I am noticing that whenever I put "FM" in front of the date (DD), it seems to affect the hour and minutes of the time as well.
So for example:
to_char(entry_date, 'Mon FMDD YYYY HH12:MIAM') converts '2014-04-01 15:07:00' to 'Apr 1 2014 3:7PM'
In this example, I would expect the result to look like: 'Apr 1 2014 03:07PM'

FM also only seems to affect the time if it is less than 10 minutes past the hour, so for example:
to_char(entry_date, 'Mon FMDD YYYY HH12:MIAM') converts '2012-04-02 9:40:00' to 'Apr 2 2012 9:40AM' will format the minutes correctly, but not the hour.
In this example, I would expect the result to look like: 'Apr 2 2012 09:40AM'

Shouldn't FM only affect the hour and not minutes, since I don't think anyone would actually want to suppress leading or trailing zeroes in minutes?

Here is what I'm seeing when I test it on our system:

We are using PostgreSQL version 9.4.1.4.
EnterpriseDB 9.4.1.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

Here's my table and the column that I'm dealing with:
Table: declared_datastream
Column: entry_date
entry_date has a type of timestamp (example): 2003-04-01 12:00:00

Please see Word attachment for examples (and highlighting) of the results of various date formatting sql queries that I ran to test.

Thank you,
Winnie Kistler

Attachment Content-Type Size
PostgreSQL Date Time Formatting Examples.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document 138.2 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2015-03-30 15:00:37 Re: pg_get_constraintdef() doesn't always give an equal constraint
Previous Message chjischj 2015-03-30 11:13:34 BUG #12917: C program created by ecpg core dumped due to “varcharsize * offset”