How to format the week number to a desired format in H2

Question

I want a date format with week number and year in the ISO format of yyyy-'W'ww. However, with only week function available in H2 database, I am able to get only the week number without the year. How can I format in such a way that I add a default year to it. Something like 2016-'W'ww.

Currently, I am using this function (which is definitely not the correct way)

WEEK(PARSEDATETIME(TRUNC(" + this.fieldName + "),'2016ww') WEEK(PARSEDATETIME(TRUNC(" + this.fieldName + "),'2016-ww')

I am not able to get what else can be done. Can anyone help me here

Answer 1

There are several valid solutions in different libraries. However, you need to know that using the standard calendar year "y" would be wrong. Instead you have to use the year-of-weekdate (or called weekbased year) with symbol "Y" (capital letter).

Example using old Calendar-stuff:

java.sql.Date sqlDate = new java.sql.Date(2017 - 1900, 0, 1); // 2017-01-01
GregorianCalendar gcal = new GregorianCalendar();
gcal.setFirstDayOfWeek(Calendar.MONDAY);
gcal.setMinimalDaysInFirstWeek(4);
gcal.setTime(sqlDate);
SimpleDateFormat sdf = new SimpleDateFormat("YYYY-'W'ww");
System.out.println(sdf.format(gcal.getTime())); // 2016-W52

---

Example using Java-8:

java.sql.Date sqlDate = new java.sql.Date(2017 - 1900, 0, 1); // 2017-01-01

LocalDate ld = sqlDate.toLocalDate();
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("YYYY-'W'ww", Locale.FRANCE);
System.out.println(dtf.format(ld)); // 2016-W52

Side note: I have here chosen the locale of France to ensure the correct week configuration needed for ISO-8601.

---

Example using my library Time4J which is only interesting and gives a surplus value if you also plan to do some arithmetic with obtained calendar week (like plusWeeks(5) or plan to get some styled localized output):

java.sql.Date sqlDate = new java.sql.Date(2017 - 1900, 0, 1); // 2017-01-01
PlainDate value = JDBCAdapter.SQL_DATE.translate(sqlDate);
CalendarWeek cw =
    CalendarWeek.of(
        value.get(PlainDate.YEAR_OF_WEEKDATE),
        value.get(Weekmodel.ISO.weekOfYear())
    );
System.out.println(cw.toString()); // 2016-W52

Answer 2

tl;dr

org.threeten.extra.YearWeek                       // Handy class found in the ThreeTen-Extra library added to your project.
.from(                                            // Determine the week number and the week-based year number from the passed `LocalDate` object, according to standard ISO 8601 definition of a week.
    myResultSet.getObject( … , LocalDate.class )  // Produces a `LocalDate` object to pass to `YearWeek.from`.  
)                                        
.toString()                                       // Generate a String in standard ISO 8601 format: yyyy-Www

2018-W13

Details

When you fetch your Date type from H2 as a java.sql.Date, convert to a java.time.LocalDate.

LocalDate ld = mySqlDate.toLocalDate();

You can interrogate for the ISO 8601 standard definition of a week where week # 1 contains the first Thursday of the year, and runs Monday-Sunday.

int weekNumber = ld.get( IsoFields.WEEK_OF_WEEK_BASED_YEAR ) ;

Extract the year.

int year = ld.getYear();

Assemble your standard ISO 8601 string.

String output = year + "-W" + String.format( "%02d ", weekNumber );

Even easier is to use the YearWeek class from the ThreeTen-Extra project.

String output = YearWeek.from( ld ).toString() ;

JDBC 4.2

As of JDBC 4.2 and later, you can directly exchange java.time objects with your database. No need to over use java.util or java.sql date-time classes again.

LocalDate ld = LocalDate.now( ZoneId.of( "Africa/Tunis" ) ) ;  // Capture the current date as seen in the wall-clock used by the people in a certain region (a time zone).
myPreparedStatement.setObject( … , ld ) ;

And retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.