On Wed, Jan 1, 2020 at 3:05 AM Bruce Momjian <br...@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
