-
Notifications
You must be signed in to change notification settings - Fork 8
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
PostgreSQL: data type of Date parameters can't always be resolved #2235
Comments
A challenge came up while following approach 1: Yielding literal values into UTC datetime properties requires timezone information to be present in Consider the following: // Date uses system's default timezone; assume 2024-01-01 12:00:00+03
Date myDate = new Date();
// expected that dateUtc will be semantically equal to myDate (2024-01-01 09:00:00+00)
yield().val(myDate).as("dateUtc")
// in reality, dateUtc becomes 2024-01-01 12:00:00+00, 3 hours later What happens during fetching is this: SELECT CAST ('2024-01-01 12:00:00+03' AS timestamp) AS dateUtc
-- equivalent to
SELECT CAST '2024-01-01 12:00:00' AS dateUtc As a result of discarding timezone information, Were there no casting, Curiously enough, this problem also manifests itself with SQL Server even though no explicit casting is performed. The reverse approach, always casting as Alternative approachTackling the described problem in full generality is a complex task as it requires us to distinguish Nevertheless, this problem might not be worth solving at the moment because:
|
Registering a custom Hibernate type mapping to enforce SQL TIMESTAMP type for DateThrough experimentation it has been discovered that it's possible to control how This can be achieved by registering a custom type mapping with Hibernate, which would then be used Internally this type mapping would need to transform the supplied It would be nice if this type mapping could be registered only when PostgreSQL is used. Although What does the specification say?JPA (aka Jakarta Persistence) 2.2 introduced support for SR-338 Mainenance Release, 7/17/17, Footnote 102 on page 431 says:
It is not understood whether this applies only to table columns or to query parameters too. JDBC 4.2, Appendix B, Section B.4, Table B-4
This section describes query parameters exclusively, which strengthens assumption (1). The said table contains the following entry:
Which means that Although, the said table also contains |
…meter binding Required to make PostgreSQL recognise such values as SQL TIMESTAMP type.
…ction This abstraction facilitates definition of datetime types relative to arbitrary time zones.
…ULL" This reverts commit 5ac7a15.
… for PostgreSQL" This reverts commit 153e2fe. The additional complexity of having a separate type mapping mechanism is unnecessary. SQL Server has proved to work correctly with this type mapping enabled.
Description
PostgreSQL driver for JDBC treats
java.util.Date
(simplyDate
henceforth) parameters as being of"unspecified" SQL type because of a timestamp vs timestampz conundrum. This behaviour is intended
to prevent unexpected rotation by the server's timezone which happens when a
Date
is used where atimestampz
is expected.By default, properties of temporal types are mapped to an SQL
timestamp
type without time zone.For PostgreSQL this would be
timestamp without time zone
. Therefore,Date
instancesshould always be resolved as
timestamp without time zone
(simplytimestamp
henceforth).There are 2 solutions to this problem:
Modify EQL-to-SQL transformation so that values of type
Date
are always cast astimestamp
.Modify Hibernate query parameterisation logic so that values of type
Date
are forced to beresolved as
timestamp
.This approach relies on knowing the internals of PostgreSQL JDBC driver's implementation of
java.sql.PreparedStatement
, the analysis of which uncovered a solution for the problem:if method
setObject
is used withjava.time.LocalDateTime
as the parameter value, then itstype will be successfully resolved as
timestamp
.Unfortunately, the API,
PreparedStatement
does not expose a method to setLocalDateTime
, thusthis solution is implementation-dependent.
Moreover, this approach requires introducing an additional PostgreSQL-specific Hibernate type
mapping for
Date
.The first approach is preferred due to being significantly simpler.
Example
Consider the following SQL query:
If parameter
mydate
is bound to an instance ofDate
, the query fails with:However, this works:
References:
org.postgresql.jdbc.PgPreparedStatement#setTimestamp(int, java.sql.Timestamp, java.util.Calendar)
Expected outcome
Parameter values of type
Date
are always resolved astimestamp
by PostgreSQL.The text was updated successfully, but these errors were encountered: