Scala - java.sql.Date is parsed incorrectly when collecting distinct values from a DataSet

Question

I'm parsing dates from a Dataset[Transaction] objects in order to collect a set of dictinct values:

val distinctTransactionDates: Set[Date] = transactions.map(t => t.transaction_date).distinct().collect().toSet

But the dates are parsed incorrectly, for example if a transaction's Date is 2019-03-31, the returned value is 2019-04-01. When I logged to check t.transaction_date.getTime it is 1553990400000 (GMT: Sunday, 31 Mar 2019, 0:00:00). But the gap for some dates vs getTime is more than one day.

The Date here is a java.sql.Date

I can't figure out how to parse dates correctly in this case in order to get distinct values without any corrections. For the above example I'm expecting to get 2019-03-31.

So 1553990400000 (which in GMT is 31 Mar 2019 00:00:00 alright) is becoming 2019-04-01? Weird. I think that [a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) would help greatly. — Ole V.V., Jul 22 '19 at 04:22
From the looks of it, the issue may be due to the timezone. Read more [here](https://stackoverflow.com/a/9303340/9524896). This is speculation since I'd need to know a lot more — Haris Nadeem, Jul 22 '19 at 05:15
Time zone alone doesn’t seem to explain the unexpected result. Only in a time zone with offset between +24:00 and +47:59 would 1553990400000 fall on April 1. I believe that offsets go up to +14:00 in practice, while java.time supports offsets up to +18:00. Also @HarisNadeem — Ole V.V., Jul 22 '19 at 10:57

Basil Bourque · Answer 1 · 2019-07-22T09:02:27.550

You are trying to extract a date-only value from a date-with-time-of-day source, but failing to account for time zone.

Another problem: You are using the terrible java.sql.Date class that was supplanted years ago by the modern java.time classes. Specifically, LocalDate.

Your date-with-time-of-day source can be referred to as a moment, a specific point on the timeline. For any given moment the time-of-day and the date both vary by time zone around the globe. Noon in Paris is not noon in Montréal. And a new day dawns earlier in the east than in the west. You must get very clear on this to do proper date-time handling. One moment in nature can be viewed in many ways through human-created notions of time zones.

First extract your moment via JDBC as an OffsetDateTime object.

Code shown here is in Java syntax rather than Scala. Also, note that java.time uses immutable objects.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Adjust into the time zone by which you want to perceive a date.

ZoneId z = ZoneId( "Asia/Tokyo" ) ;
ZonedDateTime zdt = odt.withZoneSameInstant( z ) ;

Extract the date-only portion.

LocalDate ld = zdt.toLocalDate() ;

Scala - java.sql.Date is parsed incorrectly when collecting distinct values from a DataSet

1 Answers1