0

I'm trying to solve a small bug involving date and time format.

In my application (Lang: Java) I can receive some notifications. Some of them show a card, which is reporting how much time ago it was generated. In database I have a table taking care of storing this notifications logs (with, of course, generation time in UTC).

Now I live in a timezone +02:00 from GMT timezone.

If I generate a notification NOW, generation time of notification gets stored in database with correct date and a time which is 2 hours before my actual generation time (correct since we store date and time in UTC format, respecting conventions).

By the way, reading notification after processing (I can either check it on the UI or call an endpoint which is returning notifications specs in JSON), I do see that notification was generated 2 hours ago (which is wrong, because I generated it a couple of seconds ago).

I red a lot of documentation about datetime format and I'm aware of SimpleDateFormat and parsing dates. In fact, we're using a SimpleDateFormat in order to format those dates stored in DB.

Currently, SimpleDateFormat sdf object has this pattern: yyyy-MM-dd'T'HH:mm:ss and with that, time format is wrong. I tried adding a Z, but output is then yyyy-MM-dd'T'HH:mm:ss+02:00 which is also wrong, because time red from DB should be yyyy-MM-dd'T'HH:mm:ss+00:00.

With pattern yyyy-MM-dd'T'HH:mm:ssXXX I also get +02:00. I don't really know how to get a +00:00. Do you know how I could solve this issue? Thanks a lot!

Edit: Java utils Date object is used

Zanfo96
  • 1
  • 1
  • You should go with `ZonedDateTime` with your zone, even if further on one uses Date. Then it will also go right in the winter. UTC time in the database is best, for such thing as NOW. – Joop Eggen Sep 05 '22 at 10:49
  • 1
    Welcome to Stack Overflow. Which database engine and which data type for the time in the database? Using JDBC or JPA or something else for storing and retrieving the time? It’s best to give us a [mre], please, so we can reproduce and understand what goes wrong. – Ole V.V. Sep 05 '22 at 11:20
  • 1
    You should probably use an `OffsetDateTime` in UTC for storing and retrieving and then convert to and from your own time zone in Java code. You definitely should not use neither `Date` nor `SimpleDateFormat` since they are troublesome and long outdated. – Ole V.V. Sep 05 '22 at 11:22
  • Thanks everyone in the first place. We're using JDBC connector. I red on the internet that Date object is outdated, but not much I can do for that :D I have to stick with it. I "solved" with a trick the problem by setting pattern to `yyyy-MM-dd'T'hh:mm:ss+00:00` but I guess it's not a best practice. I'm definitely checking rn for ZonedDateTime and OffsetDateTime – Zanfo96 Sep 05 '22 at 14:52
  • If you cannot avoid getting an old-fashioned `Date` from a legacy API, convert it to `OffsetDateTime` using `yourOldfashionedDate.toInstant.atOffset(ZoneOffset.UTC)`. If you indispensably need a `Date` for a legacy API, convert the `OffsetDateTime` you got from JDBC using `Date.from(offsetDateTimeFromDatabase.toInstant())`. This will prevent your `Date` from being two hours off the way you described. And don’t transfer your date and time as text to nor from the database. Always use proper date-time objects like `OffsetDateTime`. See the answer. – Ole V.V. Sep 05 '22 at 18:34

1 Answers1

1

Avoid legacy classes

You are using terrible legacy date-time classes that were years ago supplanted by the modern java.time classes defined in JSR 310.

java.time

Store your moment in a database column of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE. Do not use a column of a type akin to TIMESTAMP WITHOUT TIME ZONE.

Use OffsetDateTime class in Java with JDBC 4.2+.

Writing.

myPreparedStatement.setObject( … , OffsetDateTime.now( ZoneOffset.UTC ) ) ;

Reading.

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

Calculate elapsed time.

Duration d = Duration.between( odt.toInstant() , Instant.now() ) ;

Adjust to your desired time zone.

ZoneId z = ZoneId.of( "Europe/Berlin" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Generate text for presentation to the user.

DateTimeFormatter f = DateTimeFormatter.ofLocalizedDateTime( … ) ;
String output = zdt.format( f ) ;

All this has been covered many times already on Stack Overflow. Search to learn more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154