I would like to extract the week number as:
2015-52
from a date formatted as:
2015-12-27
How can I perform this in postgres?
my weeks are calculated from monday to sunday.
Add a comment
|
3 Answers
To get the year and the week in a single character value, use to_char()
select to_char(current_date, 'IYYY-IW');
IW returns the year and the week number as defined in the ISO standard and IYYY returns the corresponding year (which might be the previous year).
If you need the year and the week number as numbers, use extract
select extract('isoyear' from current_date) as year,
extract('week' from current_date) as week;
1 Comment
Feenaboccles
Wow, well done on amending a 3-year old answer :) I wasn't expecting a response, just wanted to leave something for the next person to see this page 👍
I have done like this
extract(week from cast(current_date as date))
extract(year from cast(current_date as date))
As same with @a_horse_with_no_name, but be careful there is a little difference between extract('isoyear' from current_date) and extract('year' from current_date) if current_date is within week0 in new year(the last week(53) of last year), especially when you extract week as well.
For example:
The follow output is 2020 not 2021
select EXTRACT('isoyear' from to_timestamp('2021-01-02 17:37:27', 'YYYY-MM-DD hh24:mn:ss')) as year
The follow output is 2021
select EXTRACT('year' from to_timestamp('2021-01-02 17:37:27', 'YYYY-MM-DD hh24:mn:ss')) as year
