CODEWITHSUNDEEP
postgresql
Steve
Steve

Reputation: 8819

Get the first date of an ISO 8601 year and week

PostgreSQL provides the extract function to get a date's year and week number according to the ISO 8601 standard, which has the first week of every year containing January 4th.

These can be extracted as follows (using today as the date):

select extract(isoyear from current_date);
select extract(week from current_date);

But there seems to be no inverse of this function. What I'm looking for is a way to get the first date in an ISO 8601 year and week. Any ideas?

Upvotes: 1

Views: 3411

Answers (3)

Luis Chaves Rodriguez
Luis Chaves Rodriguez

Reputation: 553

Why not just doing:

SELECT 
date_trunc('week', EXTRACT(YEAR FROM CURRENT_DATE) || '-01-04')::date 
AS first_monday_of_iso_year;

Upvotes: 0

Justin Rea
Justin Rea

Reputation: 1

The correct way to get the first iso date of a year is:

to_date(extract(isoyear from "Fiscal Date")::text||'-0001','iyyy-iddd')

The accepted answer seems close but is incorrect. For example this:

to_date(to_char(date_trunc('year', '1994-01-01'::date), 'iyyy-iw'), 'iyyy-iw')

returns '1993-12-27', but the correct start of iso year date is '1993-01-04'. The correct approach:

to_date(extract(isoyear from  '1994-01-01'::date)::text||'-0001','iyyy-iddd')

returns '1993-01-04'.

Upvotes: 0

user330315
user330315

Reputation:

to_date() supports the ISO year and ISO week.

So you first need to get the "start of the year" (using date_trunc()) and then convert that to the proper "week" (using to_char()) and convert that back into a date (using to_date()):

to_date(to_char(date_trunc('year', current_date), 'iyyy-iw'), 'iyyy-iw')

This statement: 

select date_trunc('year', current_date), 
       to_char(date_trunc('year', current_date), 'iyyy-iw'),
       to_date(to_char(date_trunc('year', current_date), 'iyyy-iw'), 'iyyy-iw');

When run on 2015-12-07 this returns: 

date_trunc          | to_char | to_date   
--------------------+---------+-----------
2015-01-01 00:00:00 | 2015-01 | 2014-12-29

Upvotes: 5

Related Questions