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 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
Attachments:
"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=# 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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=# 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-bitHm. 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs