Java和多SQL RDBMS之间的年度计算差异 [英] Week of year calculation differences among Java and multi SQL RDBMS

问题描述

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

首先，注意通过某些方法，在某些年份的某些时候是明年的第一个星期。示例：2012-12-31是2013年的第1周（我不记得确切的一年，但是这样）。所以我必须计算一个日期的正确的周年

在Java 中，它基于2个条件取决于语言环境：

• 一周（星期日或星期一）的第一天



• 第一周的最短日期（1或4）

   日历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中的$ code> getFirstDayOfWeek（）== SUNDAY 和 getMinimalDaysInFirstWeek == 1 ），我想在MySQL中计算相同结果

但是我尝试MySQL中的所有8模式，无匹配。我失败了，因为在MySQL里没有一个模式计算周基于第一周的最小的一天是1（只有一些模式与4），所以我手动 setMinimalDaysInFirstWeek（4）和MySQL中的模式6匹配Java中的结果

（我必须编写一个测试，比较Java和MySQL中的每两天的结果）

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

还有一个问题，我真的很害怕，如果我的系统从MySQL更改为其他RDBMS，它将有其他方法，我无法处理它们（Oracle中的示例没有WEEK函数）。那么有什么方法可以计算周满足许多RDBMS？

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

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




• 如果从MySQL传递到Java，我必须编写一个从Java中调用它的过程，并且必须解决问题找到计算周满足的方式许多RDBMS

但是，我必须计算大量的日期，这些都不符合我，任何人都有想法解决我的问题？

解决方案

ISO 8601

虽然我不在几周内有丰富的工作经验，在我看来，最聪明的方法是遵循 ISO 8601 标准。该标准显然是定义一周和周数。

标准说：

• 周开始于星期一。


• 星期几编号为 1 至 7 。星期一= 1。


• 第一周包含年度第一个星期四。


• 星期编号 01 到 53 。 （否 00 。）

MySQL中的WEEKOFYEAR（）

虽然我不知道MySQL（我是一个 Postgres 种的人），我看过这份文件日期和时间功能。

WEEK（日期，模式） 功能似乎符合ISO 8601，其中第一天是星期一，星期编号为1-53，今年有4天以上（不是官方定义，而是另外说包含第一个星期四）。

此外，MySQL提供 WEEKOFYEAR（date） 功能特别是使用模式3调用WEEK的速记。所以我建议你坚持打电话WEEKOFYEAR。

时区

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

我假设，但不知道，MySQL将日期时间值存储在 UTC 。所以我也假设通过不应用任何时区调整，调用他们的周相关功能在UTC上有效地工作。

我怀疑这可能是您问题的根源。如果忽略指定一个，则java.util.Calendar类分配JVM的默认时区。顺便说一句，这是一个重要的教训：始终指定一个时区，而不是依赖于隐式默认。所以MySQL正在按UTC计算周年，而日历正在计算其他时区（巴黎，加尔各答，无论如何），显然结果将会有所不同。

我的猜测是解决方案是：

• 一致地使用UTC执行Java工作


• 应用一个时区调整到MySQL的日期时间值（如果可能的话，我不知道）。

这些解决方案最好取决于您的业务政策。某些企业可能希望在其家庭办公时区或主要供应商/客户等的时区工作。其他业务，特别是那些在各个时区有疑虑的企业都选择以UTC为单位定义所有内容。

我怀疑在这个萎缩世界的这个时代，绝大多数人的长期选择将是始终以UTC为准。但是我不是你的公司。

Joda-Time或java.time

至于Java方面的东西，避免使用java.util.Date& .Classendar类。他们是臭名昭着的麻烦。即使是Sun / Oracle已经放弃了，他们用新的 Java 8中的nofollow> java.time包 a>。该软件包受 Joda-Time 的启发。 Joda-Time继续是一个可行的项目，而java.time和Joda-Time各有其优缺点。两者都支持ISO 8601，并且使用了许多默认行为的标准。两者都有一年的支持。 Joda-Time团队已经要求我们迁移到java.time。

许多java.time功能已经被移植到Java 6& 7在 ThreeTen-Backport 中，并进一步适应于 ThreeTenABP 。

请参阅 Oracle Tutorial 。

java.time中的示例代码（从Java 8开始）

请参阅我的答案类似的问题。

OffsetDateTime 类代表一个点时间轴， nanoseconds 的分辨率调整为偏离UTC （不是全职区）。

  OffsetDateTime twelves = OffsetDateTime.of（2012，12，12，0，0，0，0，ZoneOffset.UTC）; 
  

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

• 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 类（一个只有日期的值）让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（）; 
  

创建字符串表示。请注意，如果您希望格式化程序对其字符串生成应用时区调整，则可以向$code>附加一个呼叫。否则，将使用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 ，例如提供功能 isoyear ，周和 isodow （星期几），记录为符合ISO 8601。

I have to do some reports and have to calc week of any date in Java and in MySQL but they have different algorithms.

Firstly, note that by some methods, in some days in the end of some year is the first week of the next year. Examples: 2012-12-31 is the week 1 of year 2013 (I don't remember the exact year, but it like that). So I have to calc the right week year of a date

In Java, it bases on 2 criteria depend on locale:

• The first day of week (Sunday or Monday)

• minimal days in first week (1 or 4)

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

In MySQL, it bases on 2 criteria depend on mode (0 to 7):

• The first day of week (Sunday or Monday) (this is the same in Java)

• minimal days in first week is 4 or the first week contains the first day of week

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

And in MySQL, some modes will calc the week with strange result, examples, one year has week 0 with only 1 day, and other weeks have full 7 days (I think the week 1 is really the first week, and the week 0 is a pre-week or something). I will ignore these mode

The scenario is: I calc the week of a list of date base on my system locale (locale with setting: getFirstDayOfWeek()==SUNDAY and getMinimalDaysInFirstWeek==1) in Java and I want to calc in MySQL with the same result

But I try all 8 mode in MySQL, none matches. I fail because in MySQL there is not a mode calc week base on minimal day in first week is 1 (only some modes with 4), so I manually setMinimalDaysInFirstWeek(4) and the mode 6 in MySQL matches the result in Java

(I have to write a test, compare the result of every day in 2 years in Java and MySQL)

So My solution is, I must fix setMinimalDaysInFirstWeek(4), and if getFirstDayOfWeek() is MONDAY, I choose mode 3 in MySQL, if getFirstDayOfWeek() is SUNDAY, I choose mode 6 in MySQL. I think it is just a temporary solution and really weird.

One more problem, I am really afraid of if my system change from MySQL to other RDBMS, it will have other method and I can't handle all of them (examples in Oracle does not have WEEK function). So is there any way to calc week satisfies many RDBMS?

I have a idea is calc week in one platform (Java or MySQL) then pass the result to the other:

• If pass from Java to MySQL, I have pass the result to the query string, or build a table contains my result

• If pass from MySQL to Java, I have to write a procedure to call it from Java, and have to resolve the problem "find the way to calc week satisfies many RDBMS"

But I must calc a mass number of date, none of these satisfy me, anyone have idea to resolve my problems?

解决方案

ISO 8601

While I do not have much experience with working in weeks, it seems to me the wisest approach would be to follow the ISO 8601 standard. That standard clearly defines a week and week numbers.

The standard says:

• Week starts on a Monday.
• Days of week are numbered 1 to 7. Monday = 1.
• The first week contains the year's first Thursday.
• Weeks are numbered 01 to 53. (No 00.)

WEEKOFYEAR() in MySQL

While I do not know MySQL (I'm a Postgres kind of person), I did read this documentation on date-and-time functions.

Mode 3 of the WEEK( date, mode ) function appears to comply with ISO 8601, where first day of week is Monday, weeks are numbered 1-53, has 4 or more days this year (not official definition, but another way to say 'contains first Thursday').

Furthermore, MySQL offers the WEEKOFYEAR( date ) function specifically as shorthand for calling WEEK with mode 3. So I suggest you stick with calling WEEKOFYEAR.

Time Zone

Time zone is crucial. The date is determined by time zone. While Montéal is enjoying the last moments before midnight on the Wednesday night of 2012-12-12, in Paris Thursday has already arrived with the date being the 13th. Same moment in the history of the Universe, but different date and time.

I assume, but do not know, that MySQL stores date-time values in UTC. So I assume too that calling their week-related functions are effectively working in UTC by not applying any time zone adjustment.

I suspect this may be the root of your problem. The java.util.Calendar class assigns the JVM’s default time zone if you neglect to specify one. By the way, that's an important lesson: Always specify a time zone rather than rely on implicit default. So MySQL is calculating week-of-year by UTC and Calendar is calculating by some other time zone (Paris, Kolkata, whatever) then obviously results will be different.

My guess is that the solution is to either:

• Consistently do your Java work using UTC
• Apply a time zone adjustment to your date-time values in MySQL (if that is possible, I don't know).

Which of those solutions is best depends on your business policy. Some businesses may want to work by the time zone of their home office, or key supplier/customer, etc. Other businesses, especially those with concerns in various time zones choose to define everything in UTC.

I suspect the wisest choice in the long-term for most folks in this era of a shrinking world would be to always work in UTC. But I am not the person running your company.

Joda-Time or java.time

As for the Java side of things, avoid using the java.util.Date & .Calendar classes. They are notoriously troublesome. Even Sun/Oracle has given up on them, supplanting them with the new java.time package in Java 8. That package was inspired by Joda-Time. Joda-Time continues as a viable project, with java.time and Joda-Time each having their strengths and weaknesses. Both support ISO 8601 very well, and use the standard for many of their default behaviors. Both have support for week-of-year. The Joda-Time team has asked us to migrate to java.time.

Much of the java.time functionality has been back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP.

See Oracle Tutorial on java.time topics.

Example code in java.time (as of Java 8)

See my Answer to a similar Question.

The OffsetDateTime class represents a point on the timeline, with a resolution of nanoseconds, adjusted to an offset-from-UTC (not a full time zone).

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

The OffsetDateTime::get method lets you access any part of the value. Each part is defined as a TemporalField. The IsoFields class provides implementations of TemporalField specific to the ISO-8601 calendar system. This includes the two we need:

• IsoFields.WEEK_OF_WEEK_BASED_YEAR
• IsoFields.WEEK_BASED_YEAR

In use…

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

A time zone is an offset-from-UTC plus a set of rules for handling anomalies such as Daylight Saving Time (DST). In some time zones the day does not begin at 00:00:00.0. So we go through the LocalDate class (a date-only value) to let java.time determine the first moment of the day.

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 );

If you are sure time-of-day and time zone is irrelevant (think twice about that!), then you can use LocalDate alone as it too has a get method.

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 );

Example code in Joda-Time 2.3.

The method getWeekOfWeekYear gets the week number. The ISODateTimeFormat class has factory methods for producing formatters for the various week-related formats defined by ISO 8601.

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

Create string representations. Note that you could append a call to withZone if you want the formatter to apply a time zone adjustment to its string generation. Otherwise, the DateTime's assigned time zone is utilized.

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

Dump to console.

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

When run.

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

Portability

As for porting to database other than MySQL, I assume that sticking with the increasingly-common ISO 8601 standard will help with portability.

Postgres for example offers the functions isoyear, week, and isodow (day-of-week), documented as complying with ISO 8601.

这篇关于Java和多SQL RDBMS之间的年度计算差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！