Обсуждение: BUG #6219: date_part() function producting incorrect year

The following bug has been logged online:

Bug reference:      6219
Logged by:          Eric Vollnogel
Email address:      edvollnogel@dstsystems.com
PostgreSQL version: 9.0.4 64bit
Operating system:   Windows 7 SP 1 64bit
Description:        date_part() function producting incorrect year
Details:

The date_part('isoyear', some_column) function is not always returning the
correct result.  See below:

Example 1: (date_part returns incorrect year)

SQL:
SELECT date_part('isoyear', observation_start), observation_start FROM
service_point_observations
WHERE observation_start >= '2011-01-01'
  AND observation_start < '2011-01-02'
LIMIT 5;

Output:
2010;"2011-01-01 00:00:00-06"
2010;"2011-01-01 00:00:00-06"
2010;"2011-01-01 00:00:00-06"
2010;"2011-01-01 00:00:00-06"
2010;"2011-01-01 00:00:00-06"

Example 2: (date_part returns incorrect year)

SQL:
SELECT date_part('isoyear', observation_start), observation_start FROM
service_point_observations
WHERE observation_start >= '2011-01-02'
  AND observation_start < '2011-01-03'
LIMIT 5;

Output:
2010;"2011-01-02 00:00:00-06"
2010;"2011-01-02 00:00:00-06"
2010;"2011-01-02 00:00:00-06"
2010;"2011-01-02 00:00:00-06"
2010;"2011-01-02 00:00:00-06"

Example 2: (date_part returns CORRECT year)

SQL:
SELECT date_part('isoyear', observation_start), observation_start FROM
service_point_observations
WHERE observation_start >= '2011-01-03'
  AND observation_start < '2011-01-04'
LIMIT 5;

Output:
2011;"2011-01-03 00:00:00-06"
2011;"2011-01-03 00:00:00-06"
2011;"2011-01-03 00:00:00-06"
2011;"2011-01-03 00:00:00-06"
2011;"2011-01-03 00:00:00-06"

Thank you for your assistance,

ERIC VOLLNOGEL

On Wed, Sep 21, 2011 at 11:17 PM, Eric Vollnogel
<edvollnogel@dstsystems.com> wrote:
> The date_part('isoyear', some_column) function is not always returning the
> correct result. =A0See below:

See http://www.postgresql.org/docs/8.4/static/functions-datetime.html :
"Each ISO year begins with the Monday of the week containing the 4th
of January, so in early January or late December the ISO year may be
different from the Gregorian year. See the week field for more
information."

So ISO year 2011 starts on Monday the 3rd of January.

If you want the Gregorian year, just user 'year' instead of 'isoyear'.

--=20
Guillaume