EXTRACT(YEAR_FOR_WEEK from '2012-01-01') [CORE3715]

[firebird-automations]

Submitted by: Pierre Yager (pierrey)

I run into a problem last week week because we are now in 2012. The first week of a year is, by convention, the first week that contains the first tueday of the year. It means that if january starts on friday, saturday or sunday, these days of january are still in the week 52 of 2011.

It was the case this year : 2012-01-01 was still part of the week 52 of the year 2011.

Consider this table :

CREATE TABLE WORK_TIME( ID Integer NOT NULL, USER Integer NOT NULL, ARRIVAL TIMESTAMP, DEPART TIMESTAMP, DURATION COMPUTED BY (DateDiff(MINUTE from ARRIVAL to DEPART)), MONTH COMPUTED BY (Extract(MONTH from ARRIVAL)), WEEK COMPUTED BY (Extract(WEEK from ARRIVAL)), YEAR COMPUTED BY (Extract(YEAR from ARRIVAL)), CONSTRAINT WORK_TIME_PK PRIMARY KEY (ID) );

For users that worked on 2012 Jan. 1st, the "YEAR" column is not semantically correct for my application because the variable start-of_week was 2011-12-26.

SQL\.Text := 'select ID, USER, ARRIVAL, DEPART, DURATION ' \+
            'from WORK\_TIME ' \+
            'where \(USER< 999\) and \(YEAR=?\) and \(WEEK=?\) ' \+
            'order by USER, ARRIVAL';

  Params\.AsInteger\[0\] := YearOf\(Start\_Of\_Week\);
  Params\.AsInteger\[1\] := WeekOf\(Start\_Of\_Week\);

I miss an EXTRACT(YEAR_FOR_WEEK from ARRIVAL) function in order to make this code work. In this case, YEAR_FOR_WEEK('2012-01-01') = 2011

[firebird-automations]

Commented by: @paulvink

There are several different conventions. When extracting WEEK, Firebird follows ISO-8601: - Weeks start on Monday. - Week 1 is the first week that has the majority (i.e. 4 or more) of its days in the new year.

Since, for weeks starting on Monday, Thursday is the middle of the week, you can also say that week 1 is the week that has the year's first Thursday (not Tuesday!) in it.

What you say about January starting on Fri, Sat or Sun is correct.

I think YEAR_FOR_WEEK would be a useful addition.

BTW, please be aware that Firebird's WEEKDAY is not ISO-8601 compliant: it returns 0 for Sunday instead of 7.

[mrotteveel]

I suggest adding

• ISO_WEEK (optionally; as an alias for WEEK for consistency),
• ISO_WEEK_YEAR* to return the ISO-8601 week-year, and
• ISO_WEEK_DAY* to return the ISO-8601 week-day (that is Monday is 1, Thuesday is 2, ... Sunday is 7)

*: For consistency in naming with the current WEEKDAY and YEARDAY units, maybe ISO_WEEKYEAR and ISO_WEEKDAY should be used.

For example:

select srcdate, extract(ISO_WEEK_YEAR from srcdate), extract(ISO_WEEK from srcdate), extract(ISO_WEEK_DAY from srcdate)
from (
  select date'1977-01-01' from rdb$database
  union all select date'1977-01-02' from rdb$database
  union all select date'1977-12-31' from rdb$database
  union all select date'1978-12-31' from rdb$database
  union all select date'1979-01-01' from rdb$database
  union all select date'1979-12-30' from rdb$database
  union all select date'1979-12-31' from rdb$database
  union all select date'1980-01-01' from rdb$database
) a

This should return:

1977-01-01 | 1976 | 53 | 6 
1977-01-02 | 1976 | 53 | 7
1977-12-31 | 1977 | 52 | 6
1978-12-31 | 1978 | 52 | 7
1979-01-01 | 1979 |  1 | 1
1979-12-30 | 1979 | 52 | 7
1979-12-31 | 1980 |  1 | 1
1980-01-01 | 1980 |  1 | 2