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-rightdigit (years   1-9 would be in decade 0?) -- this is one of the colloquial   versions when people
referto "the (19)90s." * The other version tends to be less well-defined. "The 1960s"   usually conjures up images of
countercultureand the British   Invasion and such; debatably occurring around 1964-1972 (this   version used by culture
cannever be derived mathematically by a   database, but it might be worth putting out here). * Any span of
approximately10 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 :)