[
https://issues.apache.org/jira/browse/IGNITE-22056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-22056:
--------------------------------------
Description:
For dates (before 1900 year), we can get the incorrect value using the
{{ResultSet#getTimestamp}} method (for a column with Instant value (TIMESTAMP
WITH TIME ZONE).
Reproducer
{code:java}
@Test
public void testTimestamps() throws SQLException {
// Ignoring time zone.
TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
try (Connection conn = DriverManager.getConnection(URL)) {
try (Statement stmt = conn.createStatement()) {
stmt.execute(
"CREATE TABLE t (tt_id INT PRIMARY KEY, dt timestamp, ts
timestamp with local time zone);"
+ "INSERT INTO t VALUES(0, '1581-12-31 00:00:00',
'1581-12-31 00:00:00')"
);
}
try (Statement stmt = conn.createStatement()) {
try (ResultSet rs = stmt.executeQuery("select dt, ts from t")) {
assertTrue(rs.next());
Timestamp tsFromDateTime = rs.getTimestamp("dt");
Timestamp tsFromTimestamp = rs.getTimestamp("ts");
assertEquals(tsFromDateTime, tsFromTimestamp); // fails
// Expected :1581-12-31 00:00:00.0
// Actual :1581-12-21 00:00:00.0
}
}
}
}
{code}
The main problem is a poor implementation of java.sql.Timestamp.
To be specific, {{valueOf(LocalDateTime)}} and {{from(Instant)}} constructs
different timestamps, even for the same dates.
{code:java}
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
Instant instant = Instant.parse("1581-12-31T00:00:00Z");
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant,
ZoneId.of("UTC"));
Timestamp rightMillisTs = Timestamp.from(instant);
Timestamp wrongMillisTs = Timestamp.valueOf(localDateTime);
assertEquals(rightMillisTs.getTime(), wrongMillisTs.getTime()); // fails
// Expected :-12244176000000
// Actual :-12243312000000
{code}
Looks like this can be resolved converting Instant to LocalDateTime and
creating timestamp from it (in getResultSet method).
But we can't use simple
{code:java}
instant.atZone(TimeZone.getDefault().toZoneId()).toLocalDateTime()
{code}
because of [this|https://joda-time.sourceforge.net/faq.html#wrongoffset]
Something like this seems to work
{code:java}
instant.plusMillis(timeZone.getOffset(timestamp -
timeZone.getOffset(timestamp))).atZone(ZoneId.of("UTC")).toLocalDateTime()
{code}
was:
For dates (before 1900 year), we can get the incorrect value using the
{{ResultSet#getTimestamp}} method (for a column with Instant value (TIMESTAMP
WITH TIME ZONE).
Reproducer
{code:java}
@Test
public void testTimestamps() throws SQLException {
// Ignoring time zone.
TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
try (Connection conn = DriverManager.getConnection(URL)) {
try (Statement stmt = conn.createStatement()) {
stmt.execute(
"CREATE TABLE t (tt_id INT PRIMARY KEY, dt timestamp, ts
timestamp with local time zone);"
+ "INSERT INTO t VALUES(0, '1581-12-31 00:00:00',
'1581-12-31 00:00:00')"
);
}
try (Statement stmt = conn.createStatement()) {
try (ResultSet rs = stmt.executeQuery("select dt, ts from t")) {
assertTrue(rs.next());
Timestamp tsFromDateTime = rs.getTimestamp("dt");
Timestamp tsFromTimestamp = rs.getTimestamp("ts");
assertEquals(tsFromDateTime, tsFromTimestamp); // fails
// Expected :1581-12-31 00:00:00.0
// Actual :1581-12-21 00:00:00.0
}
}
}
}
{code}
The main problem is a poor implementation of java.sql.Timestamp.
To be specific, {{valueOf(LocalDateTime)}} and {{from(Instant)}} constructs
different timestamps, even for the same dates.
{code:java}
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
Instant instant = Instant.parse("1581-12-31T00:00:00Z");
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant,
ZoneId.of("UTC"));
Timestamp rightMillisTs = Timestamp.from(instant);
Timestamp wrongMillisTs = Timestamp.valueOf(localDateTime);
assertEquals(rightMillisTs.getTime(), wrongMillisTs.getTime()); // fails
// Expected :-12244176000000
// Actual :-12243312000000
{code}
Looks like this can be resolved converting Instant to LocalDateTime and
creating timestamp from it (in getResultSet method).
But we can't use simple
{{instant.atZone(TimeZone.getDefault().toZoneId()).toLocalDateTime()}} because
of [this|https://joda-time.sourceforge.net/faq.html#wrongoffset]
Something like this seems to work
{{instant.plusMillis(timeZone.getOffset(timestamp -
timeZone.getOffset(timestamp))).atZone(ZoneId.of("UTC")).toLocalDateTime()}}
> Sql. Jdbc. Incorrect value can be read for a column with Instant.
> -----------------------------------------------------------------
>
> Key: IGNITE-22056
> URL: https://issues.apache.org/jira/browse/IGNITE-22056
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> For dates (before 1900 year), we can get the incorrect value using the
> {{ResultSet#getTimestamp}} method (for a column with Instant value (TIMESTAMP
> WITH TIME ZONE).
> Reproducer
> {code:java}
> @Test
> public void testTimestamps() throws SQLException {
> // Ignoring time zone.
> TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
> try (Connection conn = DriverManager.getConnection(URL)) {
> try (Statement stmt = conn.createStatement()) {
> stmt.execute(
> "CREATE TABLE t (tt_id INT PRIMARY KEY, dt timestamp, ts
> timestamp with local time zone);"
> + "INSERT INTO t VALUES(0, '1581-12-31 00:00:00',
> '1581-12-31 00:00:00')"
> );
> }
> try (Statement stmt = conn.createStatement()) {
> try (ResultSet rs = stmt.executeQuery("select dt, ts from t")) {
> assertTrue(rs.next());
> Timestamp tsFromDateTime = rs.getTimestamp("dt");
> Timestamp tsFromTimestamp = rs.getTimestamp("ts");
> assertEquals(tsFromDateTime, tsFromTimestamp); // fails
> // Expected :1581-12-31 00:00:00.0
> // Actual :1581-12-21 00:00:00.0
> }
> }
> }
> }
> {code}
> The main problem is a poor implementation of java.sql.Timestamp.
> To be specific, {{valueOf(LocalDateTime)}} and {{from(Instant)}} constructs
> different timestamps, even for the same dates.
> {code:java}
> TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
> Instant instant = Instant.parse("1581-12-31T00:00:00Z");
> LocalDateTime localDateTime = LocalDateTime.ofInstant(instant,
> ZoneId.of("UTC"));
> Timestamp rightMillisTs = Timestamp.from(instant);
> Timestamp wrongMillisTs = Timestamp.valueOf(localDateTime);
> assertEquals(rightMillisTs.getTime(), wrongMillisTs.getTime()); // fails
> // Expected :-12244176000000
> // Actual :-12243312000000
> {code}
> Looks like this can be resolved converting Instant to LocalDateTime and
> creating timestamp from it (in getResultSet method).
> But we can't use simple
> {code:java}
> instant.atZone(TimeZone.getDefault().toZoneId()).toLocalDateTime()
> {code}
> because of [this|https://joda-time.sourceforge.net/faq.html#wrongoffset]
> Something like this seems to work
> {code:java}
> instant.plusMillis(timeZone.getOffset(timestamp -
> timeZone.getOffset(timestamp))).atZone(ZoneId.of("UTC")).toLocalDateTime()
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
