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 :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers