incognito February 2016

Query for computing yearweek

I need to a help at query in such a way that in yearweek,in a year there are 52 weeks

2015 has 52 weeks but 2016 yearweek should start as 201601 that is the first week of year 2016, not as a continuation of 2015's 53rd week.

2015 jan 1--> 201501(1st week of 2015) likewise
2016 jan 1 --> 201601(1st week of 2016) it shouldnt be 201553(53rd week of 2015)

Answers


wingedpanther February 2016

Try this

CREATE OR replace FUNCTION fn_yearofweek (val DATE)
RETURNS TEXT AS $$
SELECT extract(year FROM val)::TEXT || cast(floor((extract(doy FROM val) - 1) / 7) + 1 AS TEXT);
$$
LANGUAGE sql

Usage:

SELECT fn_yearofweek('2015-01-01')
      ,fn_yearofweek('2015-12-31')
      ,fn_yearofweek('2016-01-01');

result:

fn_yearofweek fn_yearofweek    fn_yearofweek 
------------- ---------------- ---------------- 
20151         201553           20161     


a_horse_with_no_name February 2016

Postgres supports two types of "week" calculations. The ISO standard (which is what you not want) and a week numbering where the first week starts on the first day of the year.

These two can be obtained using the to_char() function (

select to_char(date '2016-01-01', 'WW') as week_january, 
       to_char(date '2015-12-31', 'WW') as week_december,
       to_char(date '2016-01-01', 'IW') as iso_week_january, 
       to_char(date '2016-12-31', 'IW') as iso_week_december

The above returns the following:

week_january | week_december | iso_week_january | iso_week_december
-------------+---------------+------------------+------------------
01           | 53            | 53               | 52               

If you need that as a real integer rather than a string, just cast the result to integer, e.g: to_char(date '2016-01-01', 'WW')::integer

The format for to_char() is explained in the manual:
http://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE


As a side note: for the ISO week to work properly you would need to combine this with the ISO yearweek as well: to_char(date '2016-01-01', 'IYYY') will return 2015

Post Status

Asked in February 2016
Viewed 2,726 times
Voted 14
Answered 2 times

Search




Leave an answer