如何在Impala SQL中将日期转换为ISO周日期?
例如2019-12-30
,在ISO周日期日历中,其写为2020-W01-1
或2020W011
回答:
将Gordon Linoff答案标记为正确,因为它解决了问题的关键部分,即推导出ISO周日期的年部分。
对于ISO周日期的星期部分,有一个就绪函数,并且ISO周日期的天部分可以很容易地从星期日起始周转换为星期一起始周。
下面的查询包含从星期一到星期日的所有星期几:
select datecol,
concat(cast(iso_year as string),'-W',lpad(cast(iso_week as string),2,'0'),'-',cast(iso_day as string)) as iso_Year_week_date_long,
concat(cast(iso_year as string),'W',lpad(cast(iso_week as string),2,'0'),cast(iso_day as string)) as iso_Year_week_date_short
from (
SELECT datecol,
(case when weekofyear(datecol) = 1 and
date_part('year',datecol) <> date_part('year',adddate(datecol,+7))
then date_part('year',datecol) + 1
when weekofyear(datecol) in (52, 53) and
date_part('year',datecol) <> date_part('year',adddate(datecol,-7))
then date_part('year',datecol) - 1
else date_part('year',datecol)
end) as iso_year,
weekofyear(datecol) as iso_week,
1+mod(dayofweek(datecol)+5,7) as iso_day
from (
select '2021-12-31' as datecol union
select '2020-12-31' as datecol union
select '2019-12-31' as datecol union
select '2018-12-31' as datecol union
select '2017-12-31' as datecol union
select '2016-12-31' as datecol union
select '2015-12-31' as datecol union
select '2014-12-31' as datecol union
select '2013-12-31' as datecol union
select '2012-12-31' as datecol union
select '2022-01-01' as datecol union
select '2021-01-01' as datecol union
select '2020-01-01' as datecol union
select '2019-01-01' as datecol union
select '2018-01-01' as datecol union
select '2017-01-01' as datecol union
select '2016-01-01' as datecol union
select '2015-01-01' as datecol union
select '2014-01-01' as datecol union
select '2013-01-01' as datecol
) as t1
) as t2
order by datecol;
并显示1月1日如何属于
datecol |iso_year_week_date_long|iso_year_week_date_short|
----------|-----------------------|------------------------|
2014-12-31|2015-W01-3 |2015W013 |
2015-01-01|2015-W01-4 |2015W014 |
2015-12-31|2015-W53-4 |2015W534 |
2016-01-01|2015-W53-5 |2015W535 |
2016-12-31|2016-W52-6 |2016W526 |
2017-01-01|2016-W52-7 |2016W527 |
2017-12-31|2017-W52-7 |2017W527 |
2018-01-01|2018-W01-1 |2018W011 |
2018-12-31|2019-W01-1 |2019W011 |
2019-01-01|2019-W01-2 |2019W012 |
2019-12-31|2020-W01-2 |2020W012 |
2020-01-01|2020-W01-3 |2020W013 |
2020-12-31|2020-W53-4 |2020W534 |
2021-01-01|2020-W53-5 |2020W535 |
我认为Impala根据您先前的问题返回了date_part()
和的iso周extract()
。没有文档可以达到此目的。
如果是这样,则可以使用条件逻辑:
select (case when date_part(week, datecol) = 1 and
date_part(year, datecol) <> date_part(year, datecol + interval 1 week)
then date_part(year, datecol) + 1
when date_part(week, datecol) in (52, 53) and
date_part(year, datecol) <> date_part(year, datecol - interval 1 week)
then date_part(year, datecol) - 1
else date_part(year, datecol)
end) as iso_year,
date_part(week, datecol) as iso_week
否则,您可以使用以下方法获取等值年的第一天:
select (case when to_char('DD', date_trunc(year, datecol), 'DD') in ('THU', 'FRI', 'SAT', 'SUN')
then next_day(date_trunc(year, date_trunc(year, datecol)), 'Monday')
else next_day(date_trunc(year, date_trunc(year, datecol)), 'Monday') - interval 7 day
end) as iso_year_start
然后,您可以使用算术从等年开始时计算等周。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句