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