[HACKERS] Re: Proposed changing the definition of decade for date_trunc and extract
Mike Swanson wrote For a long time (since version 8.0), PostgreSQL has adopted the logical barriers for centuries and millenniums in these functions. The calendar starts millennium and century 1 on year 1, directly after 1 BC. Unfortunately decades are still reported rather simplistically by dividing the year by 10. Years 1-10 are logically the first decade and working up from there, year 2014 should be counted as 202nd decade. I've pushed code and documentation changes to reflect this, based on the master branch (9.5devel), it's on the branch new_decade_def at https://github.com/chungy/postgres.git -- In both the commit message and docs, I made note of the backwards compatibility change. I don't know how much of an impact this would have but I suspect not many applications are really going to be affected by how decades are counted (should be simple to fix on their part, if any are...). Floor ( Year / 10 ) = decade number feels right. Sure, the zero decade only has 9 years but after that everything is easy to read. Typical usage refers to decades such as the 80s and the 90s but if you start counting at 1 the 0 year would have a mis-matched prefix. And date truncation would be weird...though I haven't tested the behavior I assume it works by basically just dropping the year digit and replacing it with zero...that at least would be the desired behavior for me. Any supporting arguments for 1-10 = 1st decade other than technical perfection? I guess if you use data around and before 1AD you care about this more, and rightly so, but given sound arguments for both methods the one more useful to more users who I suspect dominantly care about years 1900. So -1 to change for breaking backward compatibility and -1 because the current behavior seems to be more useful in everyday usage. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Proposed-changing-the-definition-of-decade-for-date-trunc-and-extract-tp5813578p5813580.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Proposed changing the definition of decade for date_trunc and extract
On 08/01/2014 05:32 PM, David G Johnston wrote: Any supporting arguments for 1-10 = 1st decade other than technical perfection? I guess if you use data around and before 1AD you care about this more, and rightly so, but given sound arguments for both methods the one more useful to more users who I suspect dominantly care about years 1900. Well, I think most people in casual speech would consider The 80's to be 1980 to 1989. But if you ask a historian, the decade is 1981 to 1990 (or, if they're an American social historian, 1981 to 1988, but that's a different topic). So both ways of counting have valid, solid arguments behind them. So -1 to change for breaking backward compatibility and -1 because the current behavior seems to be more useful in everyday usage. If we were adding a new decade feature, then I'd probably side with Mike. However, it's hard for me to believe that this change is worth breaking backwards compatibility. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Proposed changing the definition of decade for date_trunc and extract
On 02/08/14 12:32, David G Johnston wrote: Mike Swanson wrote For a long time (since version 8.0), PostgreSQL has adopted the logical barriers for centuries and millenniums in these functions. The calendar starts millennium and century 1 on year 1, directly after 1 BC. Unfortunately decades are still reported rather simplistically by dividing the year by 10. Years 1-10 are logically the first decade and working up from there, year 2014 should be counted as 202nd decade. I've pushed code and documentation changes to reflect this, based on the master branch (9.5devel), it's on the branch new_decade_def at https://github.com/chungy/postgres.git -- In both the commit message and docs, I made note of the backwards compatibility change. I don't know how much of an impact this would have but I suspect not many applications are really going to be affected by how decades are counted (should be simple to fix on their part, if any are...). Floor ( Year / 10 ) = decade number feels right. Sure, the zero decade only has 9 years but after that everything is easy to read. Typical usage refers to decades such as the 80s and the 90s but if you start counting at 1 the 0 year would have a mis-matched prefix. And date truncation would be weird...though I haven't tested the behavior I assume it works by basically just dropping the year digit and replacing it with zero...that at least would be the desired behavior for me. Any supporting arguments for 1-10 = 1st decade other than technical perfection? I guess if you use data around and before 1AD you care about this more, and rightly so, but given sound arguments for both methods the one more useful to more users who I suspect dominantly care about years 1900. So -1 to change for breaking backward compatibility and -1 because the current behavior seems to be more useful in everyday usage. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Proposed-changing-the-definition-of-decade-for-date-trunc-and-extract-tp5813578p5813580.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. 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??? :-) Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Proposed changing the definition of decade for date_trunc and extract
On Fri, Aug 1, 2014 at 8:15 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 02/08/14 12:32, David G Johnston wrote: Any supporting arguments for 1-10 = 1st decade other than technical perfection? I guess if you use data around and before 1AD you care about this more, and rightly so, but given sound arguments for both methods the one more useful to more users who I suspect dominantly care about years 1900. So -1 to change for breaking backward compatibility and -1 because the current behavior seems to be more useful in everyday usage. 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 SQL, only relational logic matters. All other logic can be superseded by committee consensus. IOW - and while I have no way of checking - this seems like something that may be governed by the SQL standard...in which case adherence to that would trump mathematical logic. David J.
Re: [HACKERS] 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 :)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
