Week of year calculation differences among Java and multi SQL RDBMS

Question

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?

Answer 1

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.