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
CodePudding user response：
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.
CodePudding user response：
This could be related:
Best practices with saving datetime & timezone info in database when data is dependant on datetime
Short summary from the question above:
You should represent time zone in UTC, but also store the time zone identifier NOT the offset in numerical form. And based on that calculate correct time while displaying this data to the user, e.g. some databases support
convert_tzfunction built-in that understands time zone identifiers.