Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 51: вычисление дат

месяца оказывается ≤ номера дня анализируемой даты, «номер недели» надо увеличить на единицу. Так для 2016.10.20 дни с понедельника по пятницу будут третьим понедельником месяца, третьим вторником месяца и т.д., а суббота и воскресенье будут, соответственно, четвёртой субботой и четвёртым воскресеньем месяца.

Функция WEEKDAY в 11-й строке запроса возвращает порядковый номер дня недели, где нумерация идёт так: ПН = 0, ВТ = 1, СР = 2 и т.д. Чтобы получить более привычный человеку результат, где ПН = 1, ВТ = 2, СР = 3 и т.д., мы прибавляем к результату работы функции единицу.

На этом решение для MySQL завершено, переходим к MS SQL Server.

Здесь нам придётся компенсировать эффект «начала недели с воскресенья», который будет в большинстве случаев присутствовать по умолчанию. Теоретически в MS SQL Server можно выполнить команду SET DATEFIRST 1, которая объявит понедельник началом недели, но практически во множестве источников отмечено, что это может не сработать. Также в MS SQL Server ISO-номер недели вычисляется не так, как в MySQL (первые числа года могут оказаться не нулевой неделей, а 52-й или 53-й).

Следующий код написан для случая, когда первым днём недели MS SQL Server считает воскресенье.

MS SQL Решение 7.4.1.a

1WITH [iso_week_data] AS

2(

3SELECT CASE

11END AS [real_iso_week_of_month_start],

12CASE

13WHEN DATEPART(iso_week, [sb_start]) > DATEPART(dy, [sb_start])

14THEN 0

15ELSE DATEPART(iso_week, [sb_start])

16END AS [real_iso_week_of_this_date],

17[sb_start],

18[sb_id]

19FROM [subscriptions]

20)

21SELECT [sb_id],

22[sb_start],

23CASE

24WHEN DATEPART(dw,

31ELSE [real_iso_week_of_this_date] - [real_iso_week_of_month_start]

32END AS [W],

33DATEPART(dw, DATEADD(day, -1, [sb_start])) AS [D]

34FROM [iso_week_data]

Рассмотрение начнём со строк 29-31, в которых реализована та же самая идея, что и в строках 6-9 решения для MySQL: из номера в году недели, на которую приходится анализируемая дата, вычитается номер в году недели, на которую приходится первое число месяца, к которому относится анализируемая дата.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 530/545

Пример 51: вычисление дат

В строках 24-28 реализуется та же логика, что и в строках 4-5 решения для MySQL: сравниваются номера дней недели для анализируемой даты и первого числа месяца (если номер дня первого числа месяца оказывается ≤ номера дня анализируемой даты, «номер недели» надо увеличить на единицу).

Операция CONVERT(VARCHAR(6), [sb_start], 112) позволяет получить значение года и месяца в виде шести символов, например: 2016.10.20 даст 201610. После выполнения + '01' мы получим 20161001, т.е. первое число октября. Число 112 — очередное «магическое значение» (см. подробности в документации).

Операция DATEADD(day, -1, [sb_start]), встречающаяся множество раз во всём запросе, нужна для того, чтобы обозначить понедельник как начало недели, т.к. сейчас началом недели MS SQL Server считает воскресенье, и номер дня недели получается «некорректным» (ВС = 1, СБ = 7 и т.д.).

Остаётся рассмотреть CTE в строках 1-20: этот код нужен для того, чтобы для первых дней года MS SQL Server не вернул номер недели как 52 или 53. Для этого номер недели сравнивается с номером дня в году. Если номер недели оказывается большим, его нужно превратить в 0.

На этом решение для MS SQL Server завершено, переходим к Oracle.

Здесь нам тоже придётся компенсировать эффект «начала недели с воскресенья», который будет в большинстве случаев присутствовать по умолчанию. В Oracle можно использовать команду ALTER SESSION SET NLS_TERRITORY=Russia для установки начала дня с понедельника, но мы снова будем предполагать худшее (например, переключать этот параметр нельзя) и напишем решение для ситуации, когда первым днём недели считается воскресенье.

Oracle Решение 7.4.1.a

1WITH "iso_week_data" AS

2(

3SELECT CASE

8END AS "real_iso_week_of_month_start",

9CASE

10WHEN TO_NUMBER(TO_CHAR("sb_start", 'IW')) >

12THEN 0

13ELSE TO_NUMBER(TO_CHAR("sb_start", 'IW'))

14END AS "real_iso_week_of_this_date",

15"sb_start",

16"sb_id"

17FROM "subscriptions"

18)

19SELECT "sb_id", "sb_start",

20CASE

21WHEN (TRUNC("sb_start", 'mm') -

28END AS "W",

29"sb_start" - NEXT_DAY("sb_start" - 7, 'MON') + 1 AS "D"

30FROM "iso_week_data"

ВOracle (как и в MS SQL Server) ISO-номер недели вычисляется не так, как

вMySQL (первые числа года могут оказаться не нулевой неделей, а 52-й или 53-й)

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 531/545

Пример 51: вычисление дат

— это также придётся компенсировать дополнительными вычислениями по аналогии с решением для MS SQL Server (CTE в строках 1-18 аналогична CTE в строках 1-20 решения для MS SQL Server за исключением способа конвертации типов данных и способа получения первого числа месяца — в Oracle для этого можно использовать функцию TRUNC).

Основная часть запроса в строках 20-30 также аналогична решению для MS SQL Server за исключением способа получения корректного номера дня недели. Рассмотрим это подробнее на примере 29-й строки.

Функция NEXT_DAY возвращает дату следующего за указанной датой указанного дня недели (например, NEXT_DAY(2016.10.20, суббота) вернёт дату ближайшей субботы, т.е. 2016.10.22).

Чтобы получить номер дня недели, нам нужно узнать разницу в днях между анализируемой датой и предыдущим (предшествующим ей) понедельником, дату которого мы получаем так NEXT_DAY("sb_start" - 7, 'MON') (это выражение можно прочитать как «вернуть дату ближайшего понедельника относительно дня, на неделю в прошлом от анализируемого»).

Итак, номер дня недели (почти) готов: "sb_start" - NEXT_DAY("sb_start" - 7, 'MON'). Остаётся добавить 1, чтобы нумерация не начиналась с нуля. Так получается финальное значение номера дня недели.

На этом решение данной задачи завершено.

Решение 7.4.1.b{528}.

Решение данной задачи построено на логике обработки дат, представленной в решении 7.4.1.a{528}, а сам код функций специально написан не оптимальным, но максимально подробным образом и прокомментирован (все поясняющие результаты получены для даты 2016.10.20).

Код функции для MySQL выглядит следующим образом:

MySQL Решение 7.4.1.b

1DELIMITER $$

2CREATE FUNCTION GetWeekAndDay (date_var DATE)

3RETURNS CHAR(4)

4BEGIN

13DECLARE current_dom TINYINT; -- номер дня в месяце (20)

14SET day_number = WEEKDAY(date_var) + 1; -- нумерация идёт с 0,

16SET current_dom = DAY(date_var); -- (20)

17SET current_wn = WEEK(date_var, 5); -- про "5" см. документацию (42)

18SET first_dom_wn = WEEK(date_var - INTERVAL current_dom-1 DAY, 5); -- (39)

19SET week_number = current_wn - first_dom_wn; -- (3)

20

21IF WEEKDAY(date_var - INTERVAL current_dom-1 DAY) <= WEEKDAY(date_var)

22THEN

23SET week_number = week_number + 1;

24END IF;

25RETURN CONCAT('W', week_number, 'D', day_number); -- W3D4

26END;

27$$

28DELIMITER ;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 532/545

Пример 51: вычисление дат

Проверить работоспособность полученной функции можно выполнением следующего кода:

MySQL Решение 7.4.1.b (проверка работоспособности)

1SELECT `sb_id`,

2`sb_start`,

3GetWeekAndDay(`sb_start`) AS `DW`

4FROM `subscriptions`

Код функции для MS SQL Server выглядит следующим образом:

MS SQL Решение 7.4.1.b

1CREATE FUNCTION GetWeekAndDay(@date_var DATETIME)

2RETURNS VARCHAR(4)

3AS

4BEGIN

18DECLARE @real_iso_week_of_month_start TINYINT;

19-- ISO-номер в году недели, к которой относится первое число месяца,

20-- к которому относится анализируемая дата (39), для первых чисел

21-- января будет 0, а не 52 или 53, как это вычисляет MS SQL Server

22DECLARE @real_iso_week_of_this_date TINYINT;

23-- ISO-номер в году недели, к которой относится анализируемая дата (42),

24-- для первых чисел января будет 0, а не 52 или 53, как это

25-- вычисляет MS SQL Server

26

27SET @day_number = DATEPART(dw, DATEADD(day, -1, @date_var));

28-- @day_number = DATEPART(dw, (2016.10.20 - 1)) =

31SET @current_wn = DATEPART(iso_week, @date_var); -- 42

32SET @first_dom_wn = DATEPART(iso_week,

37SET @current_doy = DATEPART(dy, @date_var); -- 294

38SET @first_dom_doy = DATEPART(dy,

43-- Актуально, например, для 2017.01.01

44IF (@first_dom_wn > @first_dom_doy)

45SET @real_iso_week_of_month_start = 0

46ELSE SET @real_iso_week_of_month_start = @first_dom_wn;

47

48-- Актуально, например, для 2017.01.02

49IF (@current_wn > @current_doy)

50SET @real_iso_week_of_this_date = 0

51ELSE SET @real_iso_week_of_this_date = @current_wn;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 533/545

Пример 51: вычисление дат

57<= @day_number)

58SET @week_number = @week_number + 1;

59

60RETURN CONCAT('W', @week_number, 'D', @day_number); -- W3D4

61END;

Проверить работоспособность полученной функции можно выполнением следующего кода:

MS SQL Решение 7.4.1.b (проверка работоспособности)

1SELECT [sb_id],

2[sb_start],

3dbo.GetWeekAndDay([sb_start]) AS [DW]

4FROM [subscriptions];

Код функции для Oracle выглядит следующим образом:

Oracle Решение 7.4.1.b

1CREATE OR REPLACE FUNCTION GetWeekAndDayISO(date_var IN DATE)

2RETURN VARCHAR

3IS

16real_iso_week_of_month_start NUMBER(3);

17-- ISO-номер в году недели, к которой относится первое число месяца,

18-- к которому относится анализируемая дата (39), для первых чисел

19-- января будет 0, а не 52 или 53, как это вычисляет Oracle

20

21real_iso_week_of_this_date NUMBER(3);

22-- ISO-номер в году недели, к которой относится анализируемая дата (42),

23-- для первых чисел января будет 0, а не 52 или 53, как это

24-- вычисляет Oracle

25BEGIN

26day_number := date_var - NEXT_DAY(date_var - 8, 'MON') + 1;

27-- day_number := 2016.10.20 - NEXT_DAY(2016.10.20 - 8, 'MON') + 1 =

31current_wn := TO_NUMBER(TO_CHAR(date_var, 'IW')); -- 42

32first_dom_wn := TO_NUMBER(TO_CHAR(TRUNC(date_var, 'mm'), 'IW')); -- 39

33-- first_dom_wn := TO_NUMBER(TO_CHAR(TRUNC(2016.10.20, 'mm'), 'IW')) =

36current_doy := TO_NUMBER(TO_CHAR(date_var, 'DDD')); -- 294

37first_dom_doy := TO_NUMBER(TO_CHAR(TRUNC(date_var, 'mm'), 'DDD'));

38-- first_dom_doy := TO_NUMBER(TO_CHAR(TRUNC(2016.10.20, 'mm'), 'DDD')) =

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 534/545