Re: Decade indication - Mailing list pgsql-hackers

On Wed, Jan 1, 2020 at 3:05 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> Does the next decade start on 2020-01-01 or 2021-01-01?  Postgres says
> it start on the former date:
>
>         SELECT EXTRACT(DECADE FROM '2019-01-01'::date);
>          date_part
>         -----------
>                201
>
>         SELECT EXTRACT(DECADE FROM '2020-01-01'::date);
>          date_part
>         -----------
>                202
>
> but the _century_ starts on 2001-01-01, not 2000-01-01:
>
>         SELECT EXTRACT(CENTURY FROM '2000-01-01'::date);
>          date_part
>         -----------
>                 20
>
>         SELECT EXTRACT(CENTURY FROM '2001-01-01'::date);
>          date_part
>         -----------
>                 21
>
> That seems inconsistent to me.  /pgtop/src/backend/utils/adt/timestamp.c
> has this C comment:
>
>          * what is a decade wrt dates? let us assume that decade 199
>          * is 1990 thru 1999... decade 0 starts on year 1 BC, and -1
>          * is 11 BC thru 2 BC...
>
> FYI, these two URLs suggest the inconsistency is OK:
>
>         https://www.timeanddate.com/calendar/decade.html
>         https://en.wikipedia.org/wiki/Decade
>


https://en.wikipedia.org/wiki/Century says:

"Although a century can mean any arbitrary period of 100 years, there
are two viewpoints on the nature of standard centuries. One is based
on strict construction, while the other is based on popular
perspective (general usage).

According to the strict construction of the Gregorian calendar, the
1st century AD began with 1 AD and ended with 100 AD, with the same
pattern continuing onward. In this model, the n-th century
started/will start on the year (100 × n) − 99 and ends in 100 × n.
Because of this, a century will only include one year, the centennial
year, that starts with the century's number (e.g. 1900 was the last
year of the 19th century).[2]

In general usage, centuries are aligned with decades by grouping years
based on their shared digits. In this model, the 'n' -th century
started/will start on the year (100 x n) - 100 and ends in (100 x n) -
1. For example, the 20th century is generally regarded as from 1900 to
1999, inclusive. This is sometimes known as the odometer effect. The
astronomical year numbering and ISO 8601 systems both contain a year
zero, so the first century begins with the year zero, rather than the
year one."


If I had to choose I'd go with the "general usage" rule above, but I
don't think we should change behaviour now.


cheers

andrew

--
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services