#!/bin/sh # # This is a shell archive. To extract its contents, # execute this file with /bin/sh to create the file(s): # # README dayofyear.spl iso8601_weekday.spl iso8601_weeknum.spl # # This shell archive created: Wed Dec 12 17:32:51 EST 2001 # echo "Extracting file README" sed -e 's/^X//' <<\SHAR_EOF > README XSubject: Variable for Week Number? XFrom: Jonathan Leffler XDate: Tue, 11 Dec 2001 09:26:15 -0800 (PST) X XI've attached some SPL: iso8601_weeknum() that calculates a week number Xin the 2001-W50; iso8601_weekday() that gives the day of the week too Xin the format 2001-W50-2 (both for Tuesday 2001-12-11); and day_of_year() Xthat yields 345 for today. X X-- XJonathan Leffler #include XSTSM, IBM Data Management Solutions. Phone: +1 650-926-6921 XEmail: Jonathan.Leffler@Informix.com, jleffler@us.ibm.com XGuardian of DBD::Informix v1.00.PC1 -- http://dbi.perl.org X "I don't suffer from insanity; I enjoy every minute of it!" SHAR_EOF if [ `wc -c < README` -ne 687 ] then echo "Lengths do not match -- Bad Copy of README" fi echo "Extracting file dayofyear.spl" sed -e 's/^X//' <<\SHAR_EOF > dayofyear.spl X-- @(#)$Id: dayofyear.spl,v 1.1 2001/02/27 20:09:57 jleffler Exp $ X-- X-- Return day of year corresponding to date (default today) X XCREATE PROCEDURE day_of_year(dateval DATE DEFAULT TODAY) RETURNING INTEGER; X RETURN dateval - MDY(1, 1, YEAR(dateval)) + 1; XEND PROCEDURE; SHAR_EOF if [ `wc -c < dayofyear.spl` -ne 270 ] then echo "Lengths do not match -- Bad Copy of dayofyear.spl" fi echo "Extracting file iso8601_weekday.spl" sed -e 's/^X//' <<\SHAR_EOF > iso8601_weekday.spl X-- @(#)$Id: iso8601_weekday.spl,v 1.1 2001/04/03 19:34:43 jleffler Exp $ X-- X-- Calculate ISO 8601 Week Number for given date X-- Defines procedure: iso8601_weekday(). X-- Uses procedure: iso8601_weeknum(). X X{ XAccording to a summary of the ISO 8601:1988 standard "Data Elements and XInterchange Formats -- Information Interchange -- Representation of Xdates and times": X X The week notation can also be extended by a number indicating the X day of the week. For example the day 1996-12-31 which is the X Tuesday (day 2) of the first week of 1997 can also be written as X X 1997-W01-2 or 1997W012 X X for applications like industrial planning where many things like X shift rotations are organized per week and knowing the week number X and the day of the week is more handy than knowing the day of the X month. X XThis procedure uses iso8601_weeknum() to format the YYYY-Www part of the Xdate, and appends '-d' to the result, allowing for Informix's coding of XSunday as day 0 rather than day 7 as required by ISO 8601. X} X XCREATE PROCEDURE iso8601_weekday(dateval DATE DEFAULT TODAY) RETURNING CHAR(10); X DEFINE rv CHAR(10); X DEFINE dw CHAR(4); X LET dw = WEEKDAY(dateval); X IF dw = 0 THEN X LET dw = 7; X END IF; X RETURN iso8601_weeknum(dateval) || '-' || dw; XEND PROCEDURE; SHAR_EOF if [ `wc -c < iso8601_weekday.spl` -ne 1290 ] then echo "Lengths do not match -- Bad Copy of iso8601_weekday.spl" fi echo "Extracting file iso8601_weeknum.spl" sed -e 's/^X//' <<\SHAR_EOF > iso8601_weeknum.spl X-- @(#)$Id: iso8601_weeknum.spl,v 1.1 2001/02/27 20:36:25 jleffler Exp $ X-- X-- Calculate ISO 8601 Week Number for given date X-- Defines procedures: day_one_week_one() and iso8601_weeknum(). X X{ XAccording to a summary of the ISO 8601:1988 standard "Data Elements and XInterchange Formats -- Information Interchange -- Representation of Xdates and times": X X In commercial and industrial applications (delivery times, X production plans, etc.), especially in Europe, it is often required X to refer to a week of a year. Week 01 of a year is per definition X the first week which has the Thursday in this year, which is X equivalent to the week which contains the fourth day of January. In X other words, the first week of a new year is the week which has the X majority of its days in the new year. Week 01 might also contain X days from the previous year and the week before week 01 of a year is X the last week (52 or 53) of the previous year even if it contains X days from the new year. A week starts with Monday (day 1) and ends X with Sunday (day 7). For example, the first week of the year 1997 X lasts from 1996-12-30 to 1997-01-05 and can be written in standard X notation as X X 1997-W01 or 1997W01 X X The week notation can also be extended by a number indicating the X day of the week. For example the day 1996-12-31 which is the X Tuesday (day 2) of the first week of 1997 can also be written as X X 1997-W01-2 or 1997W012 X X for applications like industrial planning where many things like X shift rotations are organized per week and knowing the week number X and the day of the week is more handy than knowing the day of the X month. X XReferring to the standard itself, section 3.17 defines a calendar week: X X week, calendar: A seven day period within a calendar year, starting X on a Monday and identified by its ordinal number within the year; X the first calendar week of the year is the one that includes the X first Thursday of that year. In the Gregorian calendar, this is X equivalent to the week which includes 4 January. X XSection 5.2.3 "Date identified by Calendar week and day numbers" states: X X Calendar week is represented by two numeric digits. The first X calendar week of a year shall be identified as 01 [...] X X Day of the week is represented by one decimal digit. Monday X shall be identified as day 1 of any calendar week [...] X XSection 5.2.3.1 "Complete representation" states: X X When the application clearly identifies the need for a complete X representation of a date identified by calendar week and day X numbers, it shall be one of the alphanumeric representations as X follows, where CCYY represents a calendar year, W is the week X designator, ww represents the ordinal number of a calendar week X within the year, and D represents the ordinal number within the X calendar week. X X Basic format: CCYYWwwD X Example: 1985W155 X Extended format: CCYY-Www-D X Example: 1985-W15-5 X XBoth the summary and the formal definition are intuitively clear, but it Xis not obvious how to translate it into an algorithm. However, we can Xdeal with the problem by exhaustively enumerating the seven options for Xthe day of the week on which 1st January falls (with actual year values Xfor concreteness): X X 1st January 2001 is Monday => Week 1 starts on 2001-01-01 X 1st January 2002 is Tuesday => Week 1 starts on 2001-12-31 X 1st January 2003 is Wednesday => Week 1 starts on 2002-12-30 X 1st January 2004 is Thursday => Week 1 starts on 2003-12-29 X 1st January 2010 is Friday => Week 1 starts on 2010-01-04 X 1st January 2005 is Saturday => Week 1 starts on 2005-01-03 X 1st January 2006 is Sunday => Week 1 starts on 2006-01-02 X X(Cross-check: 1st January 1997 was a Wednesday; the summary notes state Xthat week 1 of 1997 started on 1996-12-30, which is consistent with the Xtable derived for dates in the first decade of the third millennium Xabove). X XWhen working with the Informix DATE types, bear in mind that Informix Xuses WEEKDAY values 0 = Sunday, 1 = Monday, 6 = Saturday. When the Xweekday of the first of January has the value in the LH column, you need Xto add the value in the RH column to the 1st of January to obtain the Xdate of the first day of the first week of the year. X X Weekday Offset to X 1st January 1st day of week 1 X X 0 +1 X 1 0 X 2 -1 X 3 -2 X 4 -3 X 5 +3 X 6 +2 X XThis can be written as MOD(11-w,7)-3 where w is the (Informix encoding Xof the) weekday of 1st January and the value 11 is used to ensure that Xno negative values are presented to the MOD operator. Hence, the Xexpression for the date corresponding to the 1st day (Monday) of the 1st Xweek of a given year, yyyy, is: X X d1w1 = MDY(1, 1, yyyy) + MOD(11 - WEEKDAY(MDY(1,1,yyyy)), 7) - 3 X XThis expression is encapsulated in stored procedure day_one_week_one: X} X XCREATE PROCEDURE day_one_week_one(yyyy INTEGER) RETURNING DATE; X DEFINE jan1 DATE; X LET jan1 = MDY(1, 1, yyyy); X RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3; XEND PROCEDURE; X X{ XGiven this date d1w1, we can calculate the week number of any other date Xin the same year as: X X TRUNC((dateval - d1w1) / 7) + 1 X XThe residual issues are ensuring that the wraparounds are correct. If Xthe given date is earlier than the start of the first week of the year Xthat contains it, then the date belongs to the last week of the previous Xyear. If the given date is on or after the start of the first week of Xthe next year, then the date belongs to the first week of the next year. X XGiven these observations, we can write iso8601_weeknum as shown below. X(Beware: iso8601_week_number() is too long for servers with the X18-character limit; so is day_one_of_week_one()). X XThen comes the interesting testing phase -- when do you get week 53? XOne answer is on Friday 1st January 2010, which is in 2009-W53 (as, Xindeed, is Sunday 3rd January 2010). Similarly, Saturday 1st January X2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52. X} X XCREATE PROCEDURE iso8601_weeknum(dateval DATE DEFAULT TODAY) RETURNING CHAR(8); X DEFINE rv CHAR(8); X DEFINE yyyy CHAR(4); X DEFINE ww CHAR(2); X DEFINE d1w1 DATE; X DEFINE tv DATE; X DEFINE wn INTEGER; X DEFINE yn INTEGER; X -- Calculate year and week number. X LET yn = YEAR(dateval); X LET d1w1 = day_one_week_one(yn); X IF dateval < d1w1 THEN X -- Date is in early January and is in last week of prior year X LET yn = yn - 1; X LET d1w1 = day_one_week_one(yn); X ELSE X LET tv = day_one_week_one(yn + 1); X IF dateval >= tv THEN X -- Date is in late December and is in the first week of next year X LET yn = yn + 1; X LET d1w1 = tv; X END IF; X END IF; X LET wn = TRUNC((dateval - d1w1) / 7) + 1; X -- Calculation complete: yn is year number and wn is week number. X -- Format result. X LET yyyy = yn; X IF wn < 10 THEN X LET ww = '0' || wn; X ELSE X LET ww = wn; X END IF X LET rv = yyyy || '-W' || ww; X RETURN rv; XEND PROCEDURE; SHAR_EOF if [ `wc -c < iso8601_weeknum.spl` -ne 7230 ] then echo "Lengths do not match -- Bad Copy of iso8601_weeknum.spl" fi echo "Done." exit 0