如何使日期的年和周数对齐 [英] How to get year and week number aligned for a date

问题描述

在尝试获取跨越多年的一系列日期的年份和周数时，我遇到了一些与年份开始/结束有关的问题.

While trying to get year and week number of a range of dates spanning multiple years, I am getting into some issues with the start/end of the year.

我理解 weeknumber 和 year 分别运行时的逻辑.但是，当它们组合在一起时，在某些情况下它们不会带来一致的结果，我想知道 Spark 中确保这些场景在给定周数的年份一致的情况下处理的最佳方法是什么，

I understand the logic for weeknumber and the one of year when they run separately. However, when they are combined, in some cases they don't bring consistent results and I was wondering what is the best way in Spark to make sure that those scenarios are handled with a consistent year for the given weeknumber,

例如运行:

spark.sql("select year('2017-01-01') as year, weekofyear('2017-01-01') as weeknumber").show(false)

输出:

+----+----------+
|year|weeknumber|
+----+----------+
|2017|52        |
+----+----------+

但是想要的输出是:

+----+----------+
|year|weeknumber|
+----+----------+
|2016|52        |
+----+----------+

并运行:

spark.sql("select year('2018-12-31') as year, weekofyear('2018-12-31') as weeknumber").show(false)

产生:

+----+----------+
|year|weeknumber|
+----+----------+
|2018|1         |
+----+----------+

但预期的是:

+----+----------+
|year|weeknumber|
+----+----------+
|2019|1         |
+----+----------+

代码在 Spark 2.4.2 上运行.

Code is running on Spark 2.4.2.

推荐答案

这种触发行为与 ISO 8601 定义一致.你不能改变它.但是，我可以想到一种解决方法.

This spark behavior is consistent with the ISO 8601 definition. You can not change it. However there is a workaround I could think of.

您可以先确定dayOfWeek，如果小于4，则将年份加1，如果等于4，则保持年份不变.否则将年份减一.

You can first determine dayOfWeek, and if it is less than 4, you increase the year by one, if it equals to 4 then keep the year untouched. Otherwise decrease the year by one.

以2017-01-01

sql("select case when date_format('2017-01-01', 'u') < 4 then year('2017-01-01')+1 when date_format('2017-01-01', 'u') = 4 then year('2017-01-01') else year('2017-01-01')- 1 end as year, weekofyear('2017-01-01') as weeknumber, date_format('2017-01-01', 'u') as dayOfWeek").show(false)

+----+----------+---------+
|year|weeknumber|dayOfWeek|
+----+----------+---------+
|2016|52        |7        |
+----+----------+---------+

以2018-12-31

sql("select case when date_format('2018-12-31', 'u') < 4 then year('2018-12-31')+1 when date_format('2018-12-31', 'u') = 4 then year('2018-12-31') else year('2018-12-31')- 1 end as year, weekofyear('2018-12-31') as weeknumber, date_format('2018-12-31', 'u') as dayOfWeek").show(false)

+----+----------+---------+
|year|weeknumber|dayOfWeek|
+----+----------+---------+
|2019|1         |1        |
+----+----------+---------+

这篇关于如何使日期的年和周数对齐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！