Extract giving wrong week of year
All,
I’m 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?
Thanks
Ben
[bprimrose@primrose partitioning]$ psql
Null display is "¤".
Line style is unicode.
Output format is wrapped.
Timing is on.
psql (9.3.9)
Type "help" for help.
bprimrose@home=> select extract (week from '2016-01-03
00:01:00'::timestamp);
date_part
───────────
53
(1 row)
Time: 1.128 ms
bprimrose@home=> select version();
version
─────────────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 9.3.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2
20150212 (Red Hat 4.9.2-6), 64-bit
(1 row)
Import Notes
Reply to msg id not found: 89b1247b1653b7ee7a114db58cd117c7@mail.gmail.comReference msg id not found: 89b1247b1653b7ee7a114db58cd117c7@mail.gmail.com
Ben Primrose <bprimrose@tracelink.com> writes:
I’m 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks Tom! I need to read in more detail in the future, I'd hit the
function definition, and scanned to find the keyword to get WOY.
I'll switch my code to ISOYEAR per the doc.
Ben
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, September 25, 2015 5:01 PM
To: Ben Primrose
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Extract giving wrong week of year
Ben Primrose <bprimrose@tracelink.com> writes:
I’m 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general