CODEWITHSUNDEEP
javaspring-data-jpagoogle-cloud-sqlcloudsqlgcp-cloudsql
Super Tech
Super Tech

Reputation: 1

Timestamp comparison and conversion to application server timezone issue with CloudSQL DB

Since cloudsql by default stores timestamp values in UTC format, how can we make the conversion of the timestamp between the application layer and DB.

The application server is MST time zone based and when am creating the sql query ( its a spring boot JPA query) I need to pass java.sql.Timestamp object for from and to Timestamp values

The table has data for 27th day but from the UI if one passes the timestamp range as 2024-06-27 00:00:00 to 2024-06-27 23:59:59 , am getting values returned for 26th as well in the response.

I tried creating the timestamp object with

ZoneId.of("UTC+07") in the object however while parsing the returned result set

the timestamp column ( created_at ) is printing the value with previous day timestamp for the resultset row

How can i ensure that what the user enters, the query while converting to UTC maintains the same time frame for querying and also while parsing the result

Ex: Input in UI/app (MST) - 2024-06-27 00:00:00 to 2024-06-27 23:59:59 while querying to DB it queries for same timeframe in UTC and while returning the resultset and parsing the timestamp column value gives timestamp in same day.

Upvotes: 0

Views: 32

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 103608

I need to pass java.sql.Timestamp object

I doubt that. But, if it's true, what you want is impossible - Timestamp objects just contain millis (and nanos) since the epoch, timezone info is lost.

java.util.Date, and all the sql types that extend it (java.sql.Date, java.sql.Timestamp) are quite broken and should not be used.

JDBC, which is the low level glue that makes java <-> SQL db interaction work, allows you to pass the corrected types, which are java.time.Instant, java.time.OffsetDateTime, and java.time.LocalDateTime. Unfortunately, the 'best' type for this stuff, java.time.ZonedDateTime, isn't required to work as per the JDBC spec (so, JDBC drivers are free to allow it, but don't have to) and usually does not work, which is the fault of DBs, not java (DBs like storing timezones as offsets which isn't how you store those, but java can't change how DBs work).

There is no .setLocalDateTime / .setInstant / .setOffsetDateTime method in PreparedStatement, but .setObject(someInstanceOfLocalDateTime) does work. There is no ResultSet.getLocalDateTime() method the way there is .getTimestamp() method, but ResultSet.getObject(colIdx, LocalDateTime.class) works, and, the JDBC spec in fact says it has to work or the JDBC driver isn't compliant.

I don't know if you're writing direct JDBC queries or using some abstraction layer (hibernate, JDBI, JOOQ, or similar). But, either those methods are called 'under the hood' or what you want is not possible without resorting to fragile hacks. Fix you code if you can, and if you are using an abstraction layer that demands you pass java.sql.Timestamp, get rid of that layer and find another one to use. Which might be a lot of work, but, that'd be how to fix this problem.

Upvotes: -1

Related Questions