Вычитание 1 года по ISO 8601 из даты в BigQuery
Я пытаюсь изменить значение даты, чтобы вернуться во времени ровно на 1 год по ISO-8601. Следующее не работает, но лучше всего описывает то, чего я хочу достичь:
date_add(date '2018-01-03', interval -1 isoyear)
Я попробовал преобразование строк в качестве промежуточного шага, но это тоже не сработало:
select parse_date('%G%V%u',safe_cast(safe_cast(format_date('%G%V%u',date '2018-01-03') as int64)-1000 as string))
Ошибка последнего - «Не удалось проанализировать входную строку« 2017013 »». Я не понимаю, почему это всегда должно разрешать уникальное значение даты.
Есть ли другой способ вычесть год по ISO из даты?
Ответы 1
Это дает соответствующий день предыдущего года ISO путем вычитания соответствующего количества недель из даты. Я основывал расчет на описании недели в году со страницы Википедии:
CREATE TEMP FUNCTION IsLongYear(d DATE) AS (
-- Year starting on Thursday
EXTRACT(DAYOFWEEK FROM DATE_TRUNC(d, YEAR)) = 5 OR
-- Leap year starting on Wednesday
(EXTRACT(DAY FROM DATE_ADD(DATE(EXTRACT(YEAR FROM d), 2, 28), INTERVAL 1 DAY)) = 29
AND EXTRACT(DAYOFWEEK FROM DATE_TRUNC(d, YEAR)) = 4)
);
CREATE TEMP FUNCTION PreviousIsoYear(d DATE) AS (
DATE_SUB(d, INTERVAL IF(IsLongYear(d), 53, 52) WEEK)
);
SELECT PreviousIsoYear('2018-01-03');
Это возвращает 2017-01-04, который соответствует третьему дню 2017 года ISO. 2018-01-03 - третий день 2018 года по ISO.
Это зависит от того, что вы хотите :) Вы, вероятно, можете адаптировать этот ответ, чтобы вместо этого вычесть количество недель в предыдущем году, если это даст вам желаемый результат.
Я получил его с IF (EXTRACT (isoyear from DATE_TRUNC (d, year))) = EXTRACT (year from d)), 53, 52) Спасибо!
Он проверяет, есть ли в текущем году 53 недели, но разве не следует проверять предыдущий год? Кроме того, я не уверен, что должно произойти, если номер недели ввода - неделя 53 ...