Re: Re: Proposed changing the definition of decade for date_trunc and extract

On Sat, 2014-08-02 at 15:15 +1200, Gavin Flower wrote:
> Since there was no year zero: then it follows that the first decade
> comprises years 1 to 10, and the current Millennium started in 2001 - or
> am I being too logical??? :-)

This is pretty much the reason I'm sending this patch, because it makes
mathematical sense, plus my OCD-sense tingles when Postgres handles
centuries and millenniums correctly, whereas decades are not.

I will concede if the compatibility breaks are too great, but I don't
know how many people depend on the output of this. I didn't do any
market research :) Besides, it seemed to me that if the other two were
able to be fixed (albeit ~10 years ago), there's little reason to avoid
fixing decade too.

There's a few definitions of a decade:
* Spans of ten years that start from year 1.
* Spans of ten years defined by the second-to-the-right digit (years
1-9 would be in decade 0?) -- this is one of the colloquial
versions when people refer to "the (19)90s."
* The other version tends to be less well-defined. "The 1960s"
usually conjures up images of counterculture and the British
Invasion and such; debatably occurring around 1964-1972 (this
version used by culture can never be derived mathematically by a
database, but it might be worth putting out here).
* Any span of approximately 10 years (the interval type is fine
enough for this).

I lack significant research but it's rare to hear people refer to
1990-1999 as the "199th century" in the same way they might refer to
1900-1999 (or 1901-2000) as the "20th century" -- and it's worth noting
that common usage for determining 20th/21st centuries generally follow
the mathematical logic of them, even if some people are off-by-one when
determining when they start and end.

I'd also argue that the current function basing the logic from
definition #2 has limited use even when you want to use it for such.
If you want to generate text for '(decades)s' you'd have to do:
SELECT extract('year' from date_trunc('decade', now())) || 's';
Or with my patch:
SELECT floor(extract('year' from now()) / 10) || '0s';
It's different, for sure, but I would actually think the second one is
a bit less awkward. Plus it's shorter :)