Handling of new Calendar/Date Instances for every Row of a ResultSet

Question

I want to get the day of the week for a number of dates saved in a database.

try {
        ResultSet rs = DBHelper.st.executeQuery("SELECT date FROM days ORDER BY date ASC;");
        while (rs.next()) {
            Calendar c = Calendar.getInstance();
            c.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(rs.getString(1)));
            String d = new SimpleDateFormat("EE").format(c.get(Calendar.DAY_OF_WEEK));
            System.out.println(d + ", " rs.getString(1).substring(8));
        }
    } catch(SQLException xc) {xc.printStackTrace();}

I am generating a new Calendar Instance for every row of the ResultSet but somehow the weekday parsed is the same for every date. The Calendar always gives back the day of the week which matches the first date in the list.

e.g.

Tue, 05 | 
Tue, 06
...

I am guessing that the Calendar Instance stays the same for all rows. Can someone suggest a way to make sure that Calendar c is updated for every row? My approach does not seem to cut it.

Why do you think 1) that you need a `Calendar`, and 2) that `new SimpleDateFormat("EE").format(c.get(Calendar.DAY_OF_WEEK))` is the right thing to do?! Your problem can be easily solved by retrieving the value as a `java.sql.Date` and then formatting once using a single format. — Mark Rotteveel, Apr 12 '18 at 08:43
What Java version are you on? Asking because Java 8 and later offer some better options compared to Java 6 and 7. — Ole V.V., Apr 12 '18 at 08:56

score 1 · Answer 1 · edited Apr 12 '18 at 15:47

    DateTimeFormatter dayFormatter = DateTimeFormatter.ofPattern("EEE, dd", Locale.ENGLISH);
    try {
        ResultSet rs = DBHelper.st.executeQuery("SELECT date FROM days ORDER BY date ASC;");
        while (rs.next()) {
            LocalDate date = rs.getObject(1, LocalDate.class);
            System.out.println(date.format(dayFormatter));
        }
    } catch (SQLException xc) {
        xc.printStackTrace();
    }

With Java 8 and a modern JDBC driver (JDBC 4.2 or higher) you should be able to get a LocalDate object directly from your result set. LocalDate is one of the classes of java.time, the modern Java date and time API.

The Calendar class is long outdated, and SimpleDateFormat is not only that, it is also notoriously troublesome. I recommend you avoid those classes completely.

PS Remember to close your result set when you’re done with it. Or use a try-with-resources.

If someone reading along is using Java 6 or 7, the following changes will be necessary: Add ThreeTen Backport to your project (link below). Get a java.sql.Date from the result set using its getDate method. Convert it to a org.threeten.bp.LocalDate using DateTimeUtils.toLocalDate. Also import DateTimeFormatter from org.threeten.bp.format.DateTimeFormatter.

What went wrong in your code?

c.get(Calendar.DAY_OF_WEEK) returns a code for the day of week, an int in the interval from 1 (Sunday) through 7 (Saturday). When you pass this number to SimpleDateFormat.format(Object), it is interpreted as a number of milliseconds since the Unix epoch of January 1, 1970 at 0:00 UTC. So very few milliseconds into that day. It was a Thursday, I got Thu as output from your code, I would expect you got the same if you’re in Europe/Berlin time zone or in UTC. For time zones west of UTC it was still Wednesday, and the output would be Wed. I cannot explain how you got Tue, and I have not been able to reproduce this.

Links

Oracle tutorial: Date Time explaining how to use java.time.
Java Specification Request (JSR) 310, where java.time was first described.
ThreeTen Backport project, the backport of java.timeto Java 6 and 7 (ThreeTen for JSR-310).
ThreeTenABP, Android edition of ThreeTen Backport
Question: How to use ThreeTenABP in Android Project, with a very thorough explanation.

score 0 · Answer 2 · answered Apr 12 '18 at 16:02

The Answer by Ole V.V. is correct and should be accepted.

`DayOfWeek`

Also, it sounds like you may be trying to derive a distinct list of day-of-week values touched by one or more of your dates returned in the result set.

If so, define a Set to hold DayOfWeek objects. A Set by definition eliminates duplicates. So if you add DayOfWeek.MONDAY three times, you end up with only one in the set.

An EnumSet is an implementation of Set optimizes to hold Enum objects.

Set< DayOfWeek > dows = EnumSet.noneOf( DayOfWeek.class ) ;

In that code sample seen in the Answer by Ole V.V., after obtaining the LocalDate object named date, get its day-of-week and add to our set.

DayOfWeek dow = date.getDayOfWeek() ;
dows.add( dow ) ;

Handling of new Calendar/Date Instances for every Row of a ResultSet

2 Answers2

What went wrong in your code?

Links

DayOfWeek

`DayOfWeek`