Re: to_char(timestamp, format) is changing the year!

Hi Adrain,

Thank you for the explanation. I was trying to send some data to a
Javascript library worked with ISO dates. But you are correct I wanted
YYYY not IYYY, that totally sliped by me.

Thanks,
-Steve

On 11/30/2014 4:19 PM, Adrian Klaver wrote:
> On 11/30/2014 01:05 PM, Stephen Woodbridge wrote:
>> Hi,
>>
>> I am have a problem when I format a timestamp in that it is changing the
>> year. This can't be right, so either I don't understand or I have found
>> a nasty corner case bug.
>>
>> This does not happen on all dates
>>
>> select '2014-12-31 00:00:00'::timestamp without time zone,
>> to_char('2014-12-31 00:00:00'::timestamp without time zone,
>> 'IYYY-MM-DD HH24:MI:SS');
>>
>> "2014-12-31 00:00:00";"2015-12-31 00:00:00"
>>
>> It appears that this also happens for all timestamps after "2014-12-28
>> 23:59:59" to the end of the year and then "2015-01-01 00:00:00" is ok
>> again.
>>
>> I have found this on 9.2 and 9.3.
>>
>> "PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc
>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"
>>
>> "PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"
>>
>> Any thoughts on how to work around this?
>
> Don't mix ISO and Gregorian fields:
>
> http://www.postgresql.org/docs/9.1/static/functions-formatting.html
>
> Attempting to construct a date using a mixture of ISO week and Gregorian
> date fields is nonsensical, and will cause an error. In the context of
> an ISO year, the concept of a "month" or "day of month" has no meaning.
> In the context of a Gregorian year, the ISO week has no meaning. Users
> should avoid mixing Gregorian and ISO date specifications.
>
>
> hplc=> select '2014-12-31 00:00:00'::timestamp without time zone,
> to_char('2014-12-31 00:00:00'::timestamp, 'YYYY-MM-DD HH24:MI:SS');
> timestamp | to_char
> ---------------------+---------------------
> 2014-12-31 00:00:00 | 2014-12-31 00:00:00
>
>
>>
>> Thanks,
>> -Steve
>>
>>
>
>