Thread: to_char(timestamp, format) is changing the year!
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?
Thanks,
-Steve
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.klaver@aklaver.com
On Nov 30, 2014, at 1:05 PM, Stephen Woodbridge <woodbri@swoodbridge.com> 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
You're using "IYYY" which is the "ISO year", which is based on Mondays or Thursdays or something equally useless. You
probablywant "YYYY" instead.
Cheers,
Steve
> 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-0100: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?
>
> Thanks,
> -Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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
>>
>>
>
>