CODEWITHSUNDEEP
javaspringhibernatetimestamphql
PainIsAMaster
PainIsAMaster

Reputation: 2076

Return CURRENT_TIMESTAMP value with specific timezone in Spring data JPA/Hibernate (HQL) query?

I have an Spring Boot API that uses Spring data JPA (1.5.9)/Hibernate (5.0.12) to query my PostgresQL database that is hosted on AWS as a RDS. It is set to Central Time (CST) I have some HQL (Hibernate) queries that use the CURRENT_TIMESTAMP function, but unfortunately and oddly seems to be returning UTC return values for whenever the HQL queries that use CURRENT_TIMESTAMP run.

I need a way to simply force the CURRENT_TIMESTAMP in the HQL query to be central time (CST). I was trying just querying the DB in pure SQL and something like this worked:

CURRENT_TIMESTAMP at TIME ZONE 'America/Chicago'

Unfortunately, I can't seem to get that to work in HQL, as IntelliJ/Hibernate throws a compilation error for:

<expression> GROUP, HAVING, or ORDER expected, got 'AT'

My sample HQL query I am using is:

@Query(value = "SELECT customerCoupons FROM CustomerCouponsEntity customerCoupons " 
+ "WHERE customerCoupons.couponCode = :couponCode "
+ "AND customerCoupons.expiredDate >= CURRENT_TIMESTAMP "
+ "AND customerCoupons.startDate <= CURRENT TIMESTAMP "
)

List<CustomerCouponsEntity> findByCouponCode(@Param("couponCode") String couponCode);

Any help would be greatly appreciated. I have the DB set as CST in AWS, so I didn't even expect this CURRENT_TIMESTAMP to be returning a UTC value (still doesn't make sense to me, unless its somehow using the JDBC driver TimeZone or JVM? I mean, this is a Hibernate query, so its not pure SQL right?)

Upvotes: 2

Views: 4729

Answers (2)

PainIsAMaster
PainIsAMaster

Reputation: 2076

Posting my own answer;

I tried setting the timezone in the properties/yaml per this article: https://moelholm.com/blog/2016/11/09/spring-boot-controlling-timezones-with-hibernate

but it did not work no matter what I tried. I made sure I was on hibernate 5.2.3 or greater and it wouldn't work.

I also tried adding the "AT TIMEZONE" in my HQL query but the code wouldn't compile. I guess even though this is valid SQL it doesn't work with the Hibernate SQL queries i.e.

CURRENT_TIMESTAMP at TIME ZONE 'America/Chicago'

Anyway, the only thing that seemed to work was:

@PostConstruct
  void started() {
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
  }

Upvotes: 2

nkengbeza
nkengbeza

Reputation: 365

You should try to set the hibernate timezone in your spring boot properties file. Example:

spring.jpa.properties.hibernate.jdbc.time_zone=YOUR_TIMEZONE

Ensure that the value of YOUR_TIMEZONE matches your DB timezone.

I guess this article will help

Upvotes: 2

Related Questions