Decade indication

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

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 +

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:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-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.