java - Java 和多 SQL RDBMS 之间的年度计算差异

Question

我必须做一些报告，并且必须在 Java 和 MySQL 中计算任何日期的周
但他们有不同的算法。

首先，请注意，通过某些方法，某年年底的某些天是下一年的第一周。示例:2012-12-31 是 2013 年的第 1 周(我不记得确切的年份，但就像那样)。所以我必须计算日期的正确周年

在 Java 中 ，它基于 2 个标准取决于语言环境:

一周的第一天(周日或周一)

第一周最少天数(1 或 4)

Calendar cal = Calendar.getInstance();
int javaWeek = cal.get(Calendar.WEEK_OF_YEAR);
int weekYear = cal.getWeekYear();

在 MySQL ，它基于 2 个标准取决于 模式 (0 到 7):

一周的第一天(星期日或星期一)(这在 Java 中是相同的)

第一周的最少天数为 4 或第一周包含一周的第一天

SELECT WEEK('2012-12-12', 1);
SELECT YEARWEEK('2012-12-12', 1);

而在 MySQL 中，有些模式会计算出奇怪的结果，例如，一年的第 0 周只有 1 天，而其他周有整整 7 天(我认为第 1 周确实是第一周，而第 0 周是前一周之类的)。我会忽略这些模式

场景是:我在 Java 中根据我的系统区域设置(区域设置: getFirstDayOfWeek()==SUNDAY 和 getMinimalDaysInFirstWeek==1 )计算日期列表的周，我想在 MySQL 中以相同的结果进行计算

但是我在 MySQL 中尝试了所有 8 种模式，没有一个匹配。我失败了，因为在 MySQL 中没有模式计算周基于第一周的最小日为 1(只有一些模式为 4)，所以我手动 setMinimalDaysInFirstWeek(4)并且 MySQL 中的模式 6 与 Java 中的结果匹配

(我要写一个测试，比较Java和MySQL在2年内每天的结果)

所以 我的解决方案 是，我 必须修复 setMinimalDaysInFirstWeek(4) ，如果 getFirstDayOfWeek()是星期一，我在MySQL中选择模式3，如果 getFirstDayOfWeek()是星期天，我在 MySQL 中选择模式 6。我认为这只是一个临时解决方案，真的很奇怪。

还有一个问题，我真的很害怕如果我的系统从MySQL换成其他RDBMS，它会有其他方法而我无法处理所有这些(Oracle中的示例没有WEEK功能)。那么有没有办法计算周满足许多RDBMS？

我有一个想法是在一个平台(Java 或 MySQL)中计算周，然后将结果传递给另一个:

如果从 Java 传递到 MySQL，我已经将结果传递给查询字符串，或者构建一个包含我的结果的表

如果从MySQL传到Java，我必须写一个程序从Java调用它，并且必须解决“找到计算周满足许多RDBMS的方法”的问题

但是我必须计算大量的日期，这些都不能让我满意，有人想解决我的问题吗？

Answer 1

ISO 8601

虽然我没有多少周工作经验，但在我看来最明智的方法是遵循 ISO 8601标准。那个标准清楚defines a week和周数。

标准说:

一周开始于 星期一 .

星期几编号 1至 7 .星期一 = 1。

第一周包含年份的 第一个星期四 .

周数 01至 53 . (否 00。)

MySQL 中的 WEEKOFYEAR()

虽然我不知道 MySQL(我是 Postgres 类型的人)，但我确实读过 this documentation on date-and-time functions .

WEEK( date, mode ) 的方式3函数似乎符合 ISO 8601，其中一周的第一天是星期一，周编号为 1-53，今年有 4 天或更多天(不是官方定义，而是另一种说法“包含第一个星期四”)。

此外，MySQL 提供了 WEEKOFYEAR( date ) 函数专门用作使用模式 3 调用 WEEK 的简写。所以我建议您坚持调用 WEEKOFYEAR。

时区

时区至关重要。日期由时区决定。蒙特利尔正在享受 2012 年 12 月 12 日星期三晚上午夜前的最后时刻，而在巴黎，星期四已经到了，日期是 13 日。宇宙历史上的同一时刻，但日期和时间不同。

我假设但不知道 MySQL 将日期时间值存储在 UTC 中。 .所以我也假设通过不应用任何时区调整来调用他们与周相关的函数可以有效地在 UTC 中工作。

我怀疑这可能是您问题的根源。如果您忽略指定时区，则 java.util.Calendar 类会分配 JVM 的默认时区。顺便说一句，这是一个重要的教训:始终指定时区而不是依赖隐式默认值。所以 MySQL 是按 UTC 计算一年中的一周，而 Calendar 是按其他时区(巴黎、加尔各答等)计算的，那么显然结果会有所不同。

我的猜测是解决方案是:

始终使用 UTC

完成 Java 工作

对 MySQL 中的日期时间值应用时区调整(如果可能，我不知道)。

哪种解决方案最好取决于您的业务策略。一些企业可能希望按其家庭办公室或主要供应商/客户等的时区工作。其他企业，尤其是那些关注不同时区的企业，选择以 UTC 定义所有内容。

我怀疑在这个世界萎缩的时代，对于大多数人来说，从长远来看，最明智的选择是始终在 UTC 工作。但我不是经营你们公司的人。

Joda-Time 或 java.time

至于 Java 方面，请避免使用 java.util.Date 和 .Calendar 类。他们是出了名的麻烦。甚至 Sun/Oracle 也放弃了它们，用新的 java.time package 取而代之。在 Java 8 .该包的灵感来自 Joda-Time . Joda-Time 继续作为一个可行的项目，java.time 和 Joda-Time 各有优缺点。两者都非常好地支持 ISO 8601，并在许多默认行为中使用该标准。两者都支持一年中的一周。 Joda-Time 团队要求我们迁移到 java.time。

在 ThreeTen-Backport 中，大部分 java.time 功能已向后移植到 Java 6 和 7。并在 ThreeTenABP中进一步适配Android .

见 Oracle Tutorial关于 java.time 主题。

java.time 中的示例代码(从 Java 8 开始)

见 my Answer到一个类似的问题。

OffsetDateTime class 表示时间轴上的一个点，分辨率为 nanoseconds ，调整为 offset-from-UTC (不是完整的时区)。

OffsetDateTime twelves = OffsetDateTime.of( 2012, 12, 12, 0, 0, 0, 0, ZoneOffset.UTC );

OffsetDateTime::get 方法允许您访问值的任何部分。每个部分定义为 TemporalField . IsoFields类提供了 TemporalField 的实现特定于 ISO-8601 日历系统。这包括我们需要的两个:

IsoFields.WEEK_OF_WEEK_BASED_YEAR

IsoFields.WEEK_BASED_YEAR

正在使用…

int week = twelves.get ( IsoFields.WEEK_OF_WEEK_BASED_YEAR );
int weekYear = twelves.get ( IsoFields.WEEK_BASED_YEAR );

时区是 UTC 的偏移量加上一组用于处理夏令时 (DST) 等异常的规则。在某些时区，一天不是从 00:00:00.0 开始的.所以我们通过 LocalDate class(仅限日期的值)让 java.time 确定一天中的第一个时刻。

ZoneId zoneId = ZoneId.of( "America/Montreal" );
LocalDate twelves = LocalDate.of( 2012 , 12 , 12 );
ZonedDateTime twelvesMontreal = twelves.atStartOfDay( zoneId );
int week = twelvesMontreal.get ( IsoFields.WEEK_OF_WEEK_BASED_YEAR );
int weekYear = twelvesMontreal.get ( IsoFields.WEEK_BASED_YEAR );

如果您确定时间和时区无关紧要(请三思!)，那么您可以使用 LocalDate独自一人，因为它也有 get 方法。

LocalDate twelves = LocalDate.of( 2012 , 12 , 12 );
int week = twelves.get ( IsoFields.WEEK_OF_WEEK_BASED_YEAR );
int weekYear = twelves.get ( IsoFields.WEEK_BASED_YEAR );

Joda-Time 2.3 中的示例代码。

方法 getWeekOfWeekYear 获取周数。 ISODateTimeFormat 类具有用于为 ISO 8601 定义的各种与周相关的格式生成格式化程序的工厂方法。

DateTime twelves = new DateTime( 2012, 12, 12, 0, 0, 0, DateTimeZone.UTC );
int weekOfTwelves = twelves.getWeekOfWeekyear();

创建字符串表示。请注意，您可以将调用附加到 withZone如果您希望格式化程序对其字符串生成应用时区调整。否则，将使用 DateTime 的指定时区。

String outputWeek = ISODateTimeFormat.weekyearWeek().print(  twelves );
String outputWeekDate = ISODateTimeFormat.weekDate().print(  twelves );

转储到控制台。

System.out.println( "twelves: " + twelves );
System.out.println( "weekOfTwelves: " + weekOfTwelves );
System.out.println( "outputWeek: " + outputWeek );
System.out.println( "outputWeekDate: " + outputWeekDate );

跑的时候。

twelves: 2012-12-12T00:00:00.000Z
weekOfTwelves: 50
outputWeek: 2012-W50
outputWeekDate: 2012-W50-3

可移植性

至于移植到 MySQL 以外的数据库，我认为坚持使用越来越普遍的 ISO 8601 标准将有助于提高可移植性。

Postgres例如 offers the functions isoyear , week , 和 isodow (星期几)，记录为符合 ISO 8601。