如何获得日期的年和周编号对齐 [英] 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屋！