如何在UTC中生成java.sql.Timestamp以进行查询比较? [英] How to generate a java.sql.Timestamp in UTC for a query comparison?
问题描述
我目前正在使用Spring-Boot构建一个网关，该网关从PSQL数据库读取数据.PSQL数据库 v11.3 由运行Rails 5.1.3 的Rails Web应用程序填充.Spring应用程序使用的是 Java 8 和Spring Data JPA.
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.
相关查询由Spring应用程序中的预定方法运行，该方法将当前时间与表的 updated_at 时间进行比较.查询如下:
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);
如果我从此查询中检索 SimulatedDeviceEntity 并从 getUpdatedAt 获取其时间，则它的类型为 java.sql.Timestamp .当使用 toString()进行显示时，我得到的是 2019-06-26 17:24:20.034923 ，该设备是在本地时间在 13:24 .
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());
无论我尝试什么，在其中任何一个上调用 toString 都会在我的本地时间生成时间戳，该时间比UTC晚4个小时.我已经了解到这是故意的，但是在从Rails应用程序检索的时间戳上调用 toString 时，即使它们以UTC显示.鉴于 toString 应该在本地时间显示时间戳，它以UTC显示我的 last_updated 时间的事实使我相信这里还有其他事情.
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.
推荐答案
实际上，当您比较两个时间戳时，不需要将它们转换为相同的时区.将它们作为绝对时间进行比较足够聪明.它的作用是将任何 Timestamp 转换为自1.1.1970 00:00 UTC之后经过的毫秒数的 Long 值，然后进行比较.因此，您可能不需要转换时间.它应该可以正常工作.
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.
这篇关于如何在UTC中生成java.sql.Timestamp以进行查询比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
