0

I'm currently building a gateway using Spring-Boot that reads data from a PSQL database. The PSQL database v11.3 is populated by a Rails web app, running Rails 5.1.3. The Spring app is using Java 8 and Spring Data JPA.

The relevant query is run by a scheduled method in the Spring app, that compares the current time against the updated_at times of the table. The query is as follows:

@Query("SELECT d from SimulatedDeviceEntity d WHERE d.updatedAt > :currentTime")
List<SimulatedDeviceEntity> findByLastUpdated(@Param("currentTime") Timestamp currentTime);

If I retrieve a SimulatedDeviceEntity from this query and get its time from getUpdatedAt, it is of type java.sql.Timestamp. When displayed using toString() I get 2019-06-26 17:24:20.034923 for the device that was updated in my local time at 13:24.

I've tried a variety of answers on how to generate a Timestamp in UTC, including here where my code to create the Timestamp is

LocalDateTime ldt = LocalDateTime.now();
ZonedDateTime zdt = ZonedDateTime.of(ldt, ZoneId.of("GMT"));
Timestamp currentTime = Timestamp.valueOf(zdt.toLocalDateTime());

Regardless of what I try, calling toString on any of these will generate the Timestamp in my local time, which is 4 hours behind UTC. I've come to understand that this is intentional, however when calling toString on the Timestamps retrieved from the Rails app, even then they display in UTC. Given that toString should display the Timestamp in local time, the fact that it displays for my last_updated times in UTC leads me to believe something else is at play here.

Smitto
  • 83
  • 11
  • Possible duplicate of [Java SQL Timestamp to ZonedDateTime](https://stackoverflow.com/questions/52943309/java-sql-timestamp-to-zoneddatetime) (unanswered, though). See also [Java - Convert java.time.Instant to java.sql.Timestamp without Zone offset](https://stackoverflow.com/questions/42766674/java-convert-java-time-instant-to-java-sql-timestamp-without-zone-offset). And don’t use `Timestamp`. Spring JPA should be able to handle either an `Instant`, an `OffsetDateTime` or a `LocalDateTIme`. – Ole V.V. Jun 26 '19 at 20:08
  • Thanks for the info Ole. I arrived at `Timestamp` due to the `updated_at` value automatically being cast to it, ie: `Date updatedAt = device.getUpdatedAt();` `logger.info("updatedAt class: " + updatedAt.getClass()); -> java.sql.Timestamp` Noted, I will try another type for this. – Smitto Jun 26 '19 at 21:48

1 Answers1

0

Actually when you comparing two Timestamps you don't need to convert them o the same timezone. It is smart enough to compare them as absolute times. What it does it converts any Timestamp to a Long value of milliseconds that have passed since 1.1.1970 00:00 UTC, and then compares them. So you probably do not need to convert your time. it should work as it is.

Michael Gantman
  • 7,315
  • 2
  • 19
  • 36
  • Thanks for the quick reply Michael, it definitely seems as though it should work. For whatever reason, it still appears to be adding those 4 hours to the timestamps of `updated_at`. This might be a conversion issue as those are originally of Ruby type `ActiveSupport::TimeWithZone` and they're getting cast into Timestamps when retrieved in the Spring app. Perhaps there could be a translational issue where the UTC vs Local time offset is literally being imbedded into the `Long` value of the time? – Smitto Jun 26 '19 at 19:02
  • Check the Locale and timezone set for your DB see if the time written in DB is correct. That might give you a hint – Michael Gantman Jun 26 '19 at 19:04
  • FWIW, I've read that Java stores ALL date/time related data as long values in UTC. The usual confusion arises from the fact that Java is also "locale aware" and always "prints" date/time related data in LOCAL (per the JVM) time zone. The only way around this is to use, for example, SimpleDateFormat to configure which time zone to "print" in/from. – geneSummons Jun 26 '19 at 19:21
  • I update the timezone of my PSQL db to UTC and that didn't do the trick either. Will keep digging. – Smitto Jun 26 '19 at 19:26