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

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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com