Thread: Decade indication
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
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Funnily enough I was having a conversation with my wife on exactly this as I opened your email.
If the Wikipedia article is to be trusted, the following seems fitting:
SELECT EXTRACT(ORDINAL DECADE FROM '2020-01-01'::date);
date_part
-----------
201
And the default:
SELECT EXTRACT(CARDINAL DECADE FROM '2020-01-01'::date);
date_part
-----------
202
If the Wikipedia article is to be trusted, the following seems fitting:
SELECT EXTRACT(ORDINAL DECADE FROM '2020-01-01'::date);
date_part
-----------
201
And the default:
SELECT EXTRACT(CARDINAL DECADE FROM '2020-01-01'::date);
date_part
-----------
202
On Tuesday, 31 December 2019, 16:36:02 GMT, 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
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
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
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
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
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> 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:
>> ...
>> 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...
> If I had to choose I'd go with the "general usage" rule above, but I
> don't think we should change behaviour now.
Well, yeah, that. The quoted comment dates to commit 46be0c18f of
2004-08-20, and a bit of excavation shows that it was just explaining
behavior that existed before, clear back to when Lockhart installed
all this functionality in 2001.
It's pretty darn difficult to justify changing behavior that's stood
for 18+ years, especially when the argument that it's wrong is subject
to debate. Either users think it's correct, or nobody uses this
function. In either case, nobody will thank us for changing it.
It's possible that we could add an alternate keyword for a different
decade (and/or century) definition, but I'd want to see some actual
field demand for that first.
regards, tom lane
Bruce Momjian <bruce@momjian.us> writes: > Does the next decade start on 2020-01-01 or 2021-01-01? I see Randall Munroe has weighed in on this topic: https://xkcd.com/2249/ regards, tom lane
On Wed, Jan 1, 2020 at 11:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Does the next decade start on 2020-01-01 or 2021-01-01? > > I see Randall Munroe has weighed in on this topic: > > https://xkcd.com/2249/ And the conclusion is ... the whole discussion is stupid? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes: > On Wed, Jan 1, 2020 at 11:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I see Randall Munroe has weighed in on this topic: >> https://xkcd.com/2249/ > And the conclusion is ... the whole discussion is stupid? Well, it's not terribly useful anyway. Arguments founded on an assumption that there's anything rational or consistent about human calendars tend to run into problems with that assumption. regards, tom lane
On Thu, Jan 2, 2020 at 08:52:17AM -0500, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > On Wed, Jan 1, 2020 at 11:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> I see Randall Munroe has weighed in on this topic: > >> https://xkcd.com/2249/ > > > And the conclusion is ... the whole discussion is stupid? > > Well, it's not terribly useful anyway. Arguments founded on an > assumption that there's anything rational or consistent about > human calendars tend to run into problems with that assumption. I assume there is enough agreement that decades start on 20X0 that we don't need to document that Postgres does that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Fri, 17 Jan 2020 at 17:52, Bruce Momjian <bruce@momjian.us> wrote:
I assume there is enough agreement that decades start on 20X0 that we
don't need to document that Postgres does that.
I think the inconsistency between years, decades, centuries, and millenia is worthy of documentation. In fact, it already is for EXTRACT:
It describes decade as "The year field divided by 10", whereas for century and millennium it refers to centuries and millennia beginning in '01 years. I think if I were designing EXTRACT I would probably have decades follow the pattern of century and millennium, mostly because if somebody wants year / 10 they can just write that. But I am, to say the least, not proposing any modifications to this particular API, for multiple reasons which I'm sure almost any reader of this list will agree with.