3

I am using Hibernate ,Spring Boot with Postgres. I have a time with time zone column and another date column. I try to add these two columns to get the timestamp with timezone value. But using hibernate query returns only timestamp without time zone. Is there any way to achieve the same

My Query is like

@Query(value = "select date_col + time_with_timestamp_column from myTable" , nativeQuery = true) Timestamp fetchvalue();

Priya
  • 1,096
  • 4
  • 15
  • 32
  • Tested this with a native query in psql and it correctly returns a `timestamp with time zone` (or `timestamptz` short internal name). However if that query is interpreted as JPQL, then maybe some typecasting to `timestamp` is added. Try enabling logging on the PG server to check https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries. Don't forget to disable it after, because those logs get enormous. – coladict Aug 07 '18 at 09:11

2 Answers2

3

If you are using Spring Data you can make the query native via @Query(..., native=true). This should cause the query to be executed as actual SQL instead of JPQL which may resolve the type issue

Ben M
  • 1,833
  • 1
  • 15
  • 24
  • even after adding the same, the issue still persists – Priya Aug 09 '18 at 05:25
  • Is there a reason that you have to use `Timestamp`? There are MUCH better date types in `java.time` such as `OffsetDateTime` you may have better luck using in your application. That, though, may not solve this particular problem. Additionally, you could not store the date and time as separate columns if you always need to rejoin them. Instead use a single `timestamp with time zone` col Finally you could create a DTO that has both a `LocalDate` and an `OffsetTime` and use JPA Projection to select the two distinct values into it, and then use java to make a `OffsetDataTime` from them – Ben M Aug 09 '18 at 14:18
  • can you share the reason why we should not use date and time as separate columns if we always need to rejoin them – Priya Aug 09 '18 at 14:39
  • Because of exactly the issue you're facing - what is the benefit of storing one element of data (date column, time column) in two separate columns instead of one column (datetime)? It solely adds additional complexity if you want to use them in the code as a combined datetime. – Ben M Aug 09 '18 at 14:43
  • No those 2 columns have to be used separate as well. I thought that it would fail in the context of changing timezone – Priya Aug 09 '18 at 14:46
0

Is this any use?

select to_char(current_date,'DD/MM/YYYY') as newdate, to_char(current_date,'DD/MM/YYYY')||' 13:03:00' as text_datetime, to_timestamp( to_char(current_date,'DD/MM/YYYY')||' 13:03:00', 'DD/MM/YYYY HH24:MI:SS')::timestamp with time zone
Slumdog
  • 470
  • 2
  • 4
  • Hibernate throws `postgresql.util.PSQLException: ERROR: syntax error at or near ":"` exception – Priya Aug 08 '18 at 05:31
  • OK you might have to investigate escape sequences, like in Python you might precede the problem character with a backslash. Anyway try the sql directly in Postgresql, I was just showing a method of joining date and time as varchar and converting it to datetime – Slumdog Aug 08 '18 at 20:20