Thread: Problem with date formatting and FM

Hi,

I was trying to use the data type formatting function, "to_char(timestamp, text)" to format a date, and I seem to be
runninginto a bug, unless there is some other explanation that I'm missing. I tried looking it up in the PostgreSQL
Todolist 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
PatternModifiers 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
casefor dates that are formatted to look like "Mon DD YYYY HH12:MIAM". I am assuming that the month, date, year, and
hoursare 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
aswell. 
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
minutescorrectly, 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
ortrailing 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
ranto test. 

Thank you,
Winnie Kistler

"Kistler, Winnie C." <kistlerwc@ornl.gov> writes:
> 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
aswell. 
> 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'

I can't reproduce that.

regression=# select to_char('2014-04-01 15:07:00'::timestamp, 'Mon DD YYYY HH12:MIAM');
       to_char
---------------------
 Apr 01 2014 03:07PM
(1 row)

regression=# select to_char('2014-04-01 15:07:00'::timestamp, 'Mon FMDD YYYY HH12:MIAM');
      to_char
--------------------
 Apr 1 2014 03:07PM
(1 row)

> 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

Hm.  You should be asking EDB for support then, not the PG community.
But I wonder whether EDB has modified their version to be more
Oracle-like on this point.

            regards, tom lane

Tom,

Thanks for your reply. It is odd that it is behaving differently on our
system. I tried your example instead of using the  "entry_date" column
just to be absolutely sure, and I get the same error. I also tried
toggling the "FM" but it doesn't seem to work like it does on Oracle. Will
check with EDB to see what they say.

Thank you,
Winnie Kistler

On 3/31/15 8:19 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"Kistler, Winnie C." <kistlerwc@ornl.gov> writes:
>> 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'
>
>I can't reproduce that.
>
>regression=3D# select to_char('2014-04-01 15:07:00'::timestamp, 'Mon DD
>YYYY HH12:MIAM');
>       to_char   =20
>---------------------
> Apr 01 2014 03:07PM
>(1 row)
>
>regression=3D# select to_char('2014-04-01 15:07:00'::timestamp, 'Mon FMDD
>YYYY HH12:MIAM');
>      to_char    =20
>--------------------
> Apr 1 2014 03:07PM
>(1 row)
>
>> 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
>
>Hm.  You should be asking EDB for support then, not the PG community.
>But I wonder whether EDB has modified their version to be more
>Oracle-like on this point.
>
>            regards, tom lane