0

I'm using the spring batch to read data from Postgres DB. I've expireAt column, which is of type Java.Instant on the JPA side which should be lower than the current UTC time as per my query. I'm using hibernate's CURRENT_TIMESTAMP (with combination of spring.jpa.properties.hibernate.jdbc.time_zone=UTC), but it's not providing current UTC time. I've also tried using now() function but that too, depending on the client-side current timestamp. How should I get current UTC time in hibernate?

@Bean
    fun postgresPostReader(): ItemReader<Post> {
        return JpaPagingItemReaderBuilder<Post>()
            .name("postgresPostReader")
            .entityManagerFactory(entityManagerFactory)
            .queryString("SELECT p FROM Post p WHERE p.expireAt <= CURRENT_TIMESTAMP")
            .pageSize(1000)
            .build()
    }

I thought to use Instant.now() as the current UTC time, but I'm not able to pass that parameter to the job every time when it runs.

Faramarz Afzali
  • 726
  • 1
  • 10
  • 24
Annon
  • 633
  • 7
  • 26
  • What's the timezone of your database? – Simon Martinelli Oct 04 '21 at 12:43
  • Also checkout https://stackoverflow.com/questions/16609724/using-current-time-in-utc-as-default-value-in-postgresql – Simon Martinelli Oct 04 '21 at 12:44
  • i want to use UTC time regardless of what timezone my db is in. the post you mentioned is about postgres (sql), but i am working with hibernate QL. – Annon Oct 04 '21 at 17:16
  • The question about the timezone of your database is key. It does not make sense to compare timestamps regardless of the timezone of the db. Hibernate will translate CURRENT_TIMESTAMP to the equivalent function provided by the underlying db (Postgresql in your case), which returns a value with a timezone: https://www.postgresql.org/docs/14/functions-datetime.html (section 9.9.5). So if the timezone of your db is not UTC, it should not be surprising that you get a different value. The `expireAt` column should also be "timestamp with timezone" to be correctly comparable to CURRENT_TIMESTAMP. – Mahmoud Ben Hassine Oct 05 '21 at 08:36
  • The timezone of the client side (your JVM) is also important. You either need to use a type that is timezone aware (for example `ZonedDateTime`) ,or make sure to send a query with a time value translated in the same timezone as the column to compare to in the database. – Mahmoud Ben Hassine Oct 05 '21 at 08:42
  • Thanks for detailed information. My db is not in UTC time, but I'm inserting `expireAt` value only in UTC time (via Instant class). So db only has UTC value. Issue is, when querying for expired post, I'm not sure how to use UTC time in raw HQL. I would easily achieve this if it's normal spring boot function. I believe that to compare UTC times, timezone shouldn't required. I've used CURRENT_TIMESTAMP only because I don't know what to use there. My question is, how can I pass Instant.now() value to everytime the batch run (that would avoid all this issue) – Annon Oct 05 '21 at 10:57

0 Answers0