From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ben Primrose <bprimrose(at)tracelink(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Extract giving wrong week of year |
Date: | 2015-09-25 21:01:17 |
Message-ID: | 21302.1443214877@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ben Primrose <bprimrose(at)tracelink(dot)com> writes:
> Im seeing some odd behavior from the extract function.
> Any idea why extract would think January 3rd of next year is the 53rd week
> of the year?
See the fine manual ...
http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
week
The number of the ISO 8601 week-numbering week of the year. By
definition, ISO weeks start on Mondays and the first week of a year
contains January 4 of that year. In other words, the first Thursday of
a year is in week 1 of that year.
In the ISO week-numbering system, it is possible for early-January
dates to be part of the 52nd or 53rd week of the previous year, and
for late-December dates to be part of the first week of the next
year. For example, 2005-01-01 is part of the 53rd week of year 2004,
and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31
is part of the first week of 2013. It's recommended to use the isoyear
field together with week to get consistent results.
Depending on what you want to define a "week" as, you can probably get
what you want from some other EXTRACT() property. "doy" div 7 might
help, for example.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Morgan Lloyd | 2015-09-25 21:41:25 | Listen/notify, equivalents on other DBs |
Previous Message | Ben Primrose | 2015-09-25 20:54:28 | Extract giving wrong week of year |