Как я вычисляю недельное число, учитывая дату?

ISO 8601 определяет стандарт для представления дат, времена и часовые пояса. Это определяет недели, которые запускаются в понедельник. Это также говорит Неделю, 1 из года является тем, который содержит по крайней мере 4 дня с данного года. Следовательно, 29-м, 30-го и 31-го декабря 20xx мог быть на неделе 1 из 20xy (где xy = xx + 1), и 1-е, 2-го и 3-го января 20xy могло все быть на прошлой неделе 20xx. Далее, может быть неделя 53.

[ Добавленный : обратите внимание, что стандарт C и 'strftime () функция обеспечивает в течение многих недель, которые запускаются в воскресенье, а также недели, которые запускаются в понедельник. Не ясно, что стандарт C предусматривает число года недели 0 в течение основанных на воскресенье недель. См. также ответ от Emerick Rogul.]

Тогда прибывает интересная фаза тестирования - когда Вы получаете неделю 53? Один ответ в пятницу 1-го января 2010, который находится в 2009-W53 (как, действительно, воскресенье 3-го января 2010). Точно так же суббота 1-го января 2005 находится в 2004-W53, но воскресенье 1-го января 2006 находится в 2005-W52.

, Который является выпиской из комментария в следующем коде, который находится на самом деле в SPL Informix (Язык хранимых процедур), но читаемо - хотя, вероятно, не перезаписываемый - без гораздо дальше объяснения. '||' оператор является операцией конкатенации строк SQL, и воскресенье является днем 0, понедельник является днем 1... Суббота является днем 6 из недели. Существуют обширные примечания в комментариях, включая соответствующий текст из стандарта. Комментарии строки запускаются' --'; возможно многострочные комментарии запускаются с' {' и конец в следующем' }'.

-- @(#)$Id: iso8601_weekday.spl,v 1.1 2001/04/03 19:34:43 jleffler Exp $
--
-- Calculate ISO 8601 Week Number for given date
-- Defines procedure: iso8601_weekday().
-- Uses procedure: iso8601_weeknum().

{
According to a summary of the ISO 8601:1988 standard "Data Elements and
Interchange Formats -- Information Interchange -- Representation of
dates and times":

    The week notation can also be extended by a number indicating the
    day of the week.  For example the day 1996-12-31 which is the
    Tuesday (day 2) of the first week of 1997 can also be written as

        1997-W01-2 or 1997W012

    for applications like industrial planning where many things like
    shift rotations are organized per week and knowing the week number
    and the day of the week is more handy than knowing the day of the
    month.

This procedure uses iso8601_weeknum() to format the YYYY-Www part of the
date, and appends '-d' to the result, allowing for Informix's coding of
Sunday as day 0 rather than day 7 as required by ISO 8601.
}

CREATE PROCEDURE iso8601_weekday(dateval DATE DEFAULT TODAY) RETURNING CHAR(10);
    DEFINE rv CHAR(10);
    DEFINE dw CHAR(4);
    LET dw = WEEKDAY(dateval);
    IF dw = 0 THEN
            LET dw = 7;
    END IF;
    RETURN iso8601_weeknum(dateval) || '-' || dw;
END PROCEDURE;
-- @(#)$Id: iso8601_weeknum.spl,v 1.1 2001/02/27 20:36:25 jleffler Exp $
--
-- Calculate ISO 8601 Week Number for given date
-- Defines procedures: day_one_week_one() and iso8601_weeknum().

{
According to a summary of the ISO 8601:1988 standard "Data Elements and
Interchange Formats -- Information Interchange -- Representation of
dates and times":

    In commercial and industrial applications (delivery times,
    production plans, etc.), especially in Europe, it is often required
    to refer to a week of a year.  Week 01 of a year is per definition
    the first week which has the Thursday in this year, which is
    equivalent to the week which contains the fourth day of January.  In
    other words, the first week of a new year is the week which has the
    majority of its days in the new year.  Week 01 might also contain
    days from the previous year and the week before week 01 of a year is
    the last week (52 or 53) of the previous year even if it contains
    days from the new year.  A week starts with Monday (day 1) and ends
    with Sunday (day 7).  For example, the first week of the year 1997
    lasts from 1996-12-30 to 1997-01-05 and can be written in standard
    notation as

        1997-W01 or 1997W01

    The week notation can also be extended by a number indicating the
    day of the week.  For example the day 1996-12-31 which is the
    Tuesday (day 2) of the first week of 1997 can also be written as

        1997-W01-2 or 1997W012

    for applications like industrial planning where many things like
    shift rotations are organized per week and knowing the week number
    and the day of the week is more handy than knowing the day of the
    month.

Referring to the standard itself, section 3.17 defines a calendar week:

    week, calendar: A seven day period within a calendar year, starting
    on a Monday and identified by its ordinal number within the year;
    the first calendar week of the year is the one that includes the
    first Thursday of that year.  In the Gregorian calendar, this is
    equivalent to the week which includes 4 January.

Section 5.2.3 "Date identified by Calendar week and day numbers" states:

    Calendar week is represented by two numeric digits.  The first
    calendar week of a year shall be identified as 01 [...]

    Day of the week is represented by one decimal digit.  Monday
    shall be identified as day 1 of any calendar week [...]

Section 5.2.3.1 "Complete representation" states:

    When the application clearly identifies the need for a complete
    representation of a date identified by calendar week and day
    numbers, it shall be one of the alphanumeric representations as
    follows, where CCYY represents a calendar year, W is the week
    designator, ww represents the ordinal number of a calendar week
    within the year, and D represents the ordinal number within the
    calendar week.

    Basic format: CCYYWwwD
        Example: 1985W155
    Extended format: CCYY-Www-D
        Example: 1985-W15-5

Both the summary and the formal definition are intuitively clear, but it
is not obvious how to translate it into an algorithm.  However, we can
deal with the problem by exhaustively enumerating the seven options for
the day of the week on which 1st January falls (with actual year values
for concreteness):

    1st January 2001 is Monday    => Week 1 starts on 2001-01-01
    1st January 2002 is Tuesday   => Week 1 starts on 2001-12-31
    1st January 2003 is Wednesday => Week 1 starts on 2002-12-30
    1st January 2004 is Thursday  => Week 1 starts on 2003-12-29
    1st January 2010 is Friday    => Week 1 starts on 2010-01-04
    1st January 2005 is Saturday  => Week 1 starts on 2005-01-03
    1st January 2006 is Sunday    => Week 1 starts on 2006-01-02

(Cross-check: 1st January 1997 was a Wednesday; the summary notes state
that week 1 of 1997 started on 1996-12-30, which is consistent with the
table derived for dates in the first decade of the third millennium
above).

When working with the Informix DATE types, bear in mind that Informix
uses WEEKDAY values 0 = Sunday, 1 = Monday, 6 = Saturday.  When the
weekday of the first of January has the value in the LH column, you need
to add the value in the RH column to the 1st of January to obtain the
date of the first day of the first week of the year.

    Weekday         Offset to
    1st January     1st day of week 1

    0               +1
    1                0
    2               -1
    3               -2
    4               -3
    5               +3
    6               +2

This can be written as MOD(11-w,7)-3 where w is the (Informix encoding
of the) weekday of 1st January and the value 11 is used to ensure that
no negative values are presented to the MOD operator.  Hence, the
expression for the date corresponding to the 1st day (Monday) of the 1st
week of a given year, yyyy, is:

    d1w1 = MDY(1, 1, yyyy) + MOD(11 - WEEKDAY(MDY(1,1,yyyy)), 7) - 3

This expression is encapsulated in stored procedure day_one_week_one:
}

CREATE PROCEDURE day_one_week_one(yyyy INTEGER) RETURNING DATE;
    DEFINE jan1 DATE;
    LET jan1 = MDY(1, 1, yyyy);
    RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
END PROCEDURE;

{
Given this date d1w1, we can calculate the week number of any other date
in the same year as:

    TRUNC((dateval - d1w1) / 7) + 1

The residual issues are ensuring that the wraparounds are correct.  If
the given date is earlier than the start of the first week of the year
that contains it, then the date belongs to the last week of the previous
year.  If the given date is on or after the start of the first week of
the next year, then the date belongs to the first week of the next year.

Given these observations, we can write iso8601_weeknum as shown below.
(Beware: iso8601_week_number() is too long for servers with the
18-character limit; so is day_one_of_week_one()).

Then comes the interesting testing phase -- when do you get week 53?
One answer is on Friday 1st January 2010, which is in 2009-W53 (as,
indeed, is Sunday 3rd January 2010).  Similarly, Saturday 1st January
2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52.
}

CREATE PROCEDURE iso8601_weeknum(dateval DATE DEFAULT TODAY) RETURNING CHAR(8);
    DEFINE rv CHAR(8);
    DEFINE yyyy CHAR(4);
    DEFINE ww CHAR(2);
    DEFINE d1w1 DATE;
    DEFINE tv DATE;
    DEFINE wn INTEGER;
    DEFINE yn INTEGER;
    -- Calculate year and week number.
    LET yn = YEAR(dateval);
    LET d1w1 = day_one_week_one(yn);
    IF dateval < d1w1 THEN
        -- Date is in early January and is in last week of prior year
        LET yn = yn - 1;
        LET d1w1 = day_one_week_one(yn);
    ELSE
        LET tv = day_one_week_one(yn + 1);
        IF dateval >= tv THEN
            -- Date is in late December and is in the first week of next year
            LET yn = yn + 1;
            LET d1w1 = tv;
        END IF;
    END IF;
    LET wn = TRUNC((dateval - d1w1) / 7) + 1;
    -- Calculation complete: yn is year number and wn is week number.
    -- Format result.
    LET yyyy = yn;
    IF wn < 10 THEN
        LET ww = '0' || wn;
    ELSE
        LET ww = wn;
    END IF
    LET rv = yyyy || '-W' || ww;
    RETURN rv;
END PROCEDURE;

Для полноты, обратную функцию также легко записать с эти day_one_week_one() функция выше:

-- @(#)$Id: ywd_date.spl,v 1.1 2012/12/29 05:13:27 jleffler Exp $
-- @(#)Create ywd_date() and ywdstr_date() stored procedures

-- Convert a date in format year, week, day (ISO 8601) to DATE.
-- Two variants:
-- ywd_date(yyyy SMALLINT, ww SMALLINT, dd SMALLINT) RETURNING DATE;
-- ywdstr_date(ywd CHAR(10)) RETURNING DATE;

-- NB: If week 53 is supplied, there is no check that the year had week
--     53 (GIGO).
-- NB: If year yyyy is a leap year and yyyy-01-01 falls on Wed (3) or
--     Thu (4), there are 53 weeks in the year.
-- NB: If year yyyy is not a leap year and yyyy-01-01 falls on Thu (4),
--     there are 53 weeks in the year.

CREATE PROCEDURE ywd_date(yyyy SMALLINT, ww SMALLINT, dd SMALLINT) RETURNING DATE AS date;
    DEFINE d DATE;
    -- Check ranges
    IF yyyy < 1 OR yyyy > 9999 OR ww < 1 OR ww > 53 OR dd < 1 OR dd > 7 THEN
        RETURN NULL;
    END IF;
    LET d = day_one_week_one(yyyy);
    LET d = d + (ww - 1) * 7 + (dd - 1);
    RETURN d;
END PROCEDURE;

-- Input: 2012-W52-5
CREATE PROCEDURE ywdstr_date(ywd CHAR(10)) RETURNING DATE AS date;
    DEFINE yyyy SMALLINT;
    DEFINE ww   SMALLINT;
    DEFINE dd   SMALLINT;
    LET yyyy = SUBSTR(ywd,  1, 4);
    LET ww   = SUBSTR(ywd,  7, 2);
    LET dd   = SUBSTR(ywd, 10, 1);
    RETURN ywd_date(yyyy, ww, dd);
END PROCEDURE;

CREATE TEMP TABLE test_dates(d DATE);
INSERT INTO test_dates VALUES('2011-12-28');
INSERT INTO test_dates VALUES('2011-12-29');
INSERT INTO test_dates VALUES('2011-12-30');
INSERT INTO test_dates VALUES('2011-12-31');
INSERT INTO test_dates VALUES('2012-01-01');
INSERT INTO test_dates VALUES('2012-01-02');
INSERT INTO test_dates VALUES('2012-01-03');
INSERT INTO test_dates VALUES('2012-01-04');
INSERT INTO test_dates VALUES('2012-01-05');
INSERT INTO test_dates VALUES('2012-01-06');
INSERT INTO test_dates VALUES('2012-01-07');

SELECT d, iso8601_weeknum(d), iso8601_weekday(d), ywdstr_date(iso8601_weekday(d))
  FROM test_dates
 ORDER BY d;

, Как отмечено в комментариях, код примет неделю 53 даты, даже если год должен только принять 52 недели.