Den 2012-01-03 15:11 skrev Pierre Yager (JIRA) såhär: > EXTRACT(YEAR_FOR_WEEK from '2012-01-01') > ---------------------------------------- > > Key: CORE-3715 > URL: http://tracker.firebirdsql.org/browse/CORE-3715 > Project: Firebird Core > Issue Type: Improvement > Components: Engine > Affects Versions: 2.1.4 > Environment: All known > Reporter: Pierre Yager > > > 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
If this function is implemented, please make sure it supports the different week numbering standards that exist. For example, according to ISO 8601 I think the first week of a year is the first week that has four or more days on the new year. This seems to contradict what Pierre wrote above. There are also other historical numbering schemes, and I believe the US has a different numbering scheme. A parameter to control desired numbering scheme would be required. Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ Write once. Port to many. Get the SDK and tools to simplify cross-platform app development. Create new or port existing apps to sell to consumers worldwide. Explore the Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join http://p.sf.net/sfu/intel-appdev Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
