Skip to content
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

Nanoseconds are rounded incorrectly 42.2.2 #1211

Closed
lukeindykiewicz opened this Issue Jun 6, 2018 · 4 comments

Comments

Projects
None yet
2 participants
@lukeindykiewicz
Copy link

lukeindykiewicz commented Jun 6, 2018

Nanoseconds passed to pgjdbc using java.sql.Timestamp are rounded incorrectly.
Timestamp 2018-06-03T23:59:59.999999999 produces 2018-06-03 23:59:59.1 in database, but should be 2018-06-03T23:59:59.999999 (that was in 42.1.1) or 2018-06-04 00:00:00 (if rounded correctly).

Code to reproduce:

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDateTime;

public class MinimalJava {

  public static void main(String[] args) throws Exception {
    LocalDateTime end = LocalDateTime.now().toLocalDate().atStartOfDay().minusNanos(1);
    runJdbcPrepared(end, "in code: " + end);
  }

  private static void runJdbcPrepared(LocalDateTime date, String msg) throws Exception {
    Timestamp timestamp = Timestamp.valueOf(date);
    String url = "jdbc:postgresql://localhost:5432/test_bug";
    Connection conn = DriverManager.getConnection(url, "test_bug_user", "pass");
    PreparedStatement ps = conn.prepareStatement("insert into bug_test(date, msg) VALUES(?,?)");
    ps.setTimestamp(1, timestamp);
    ps.setString(2, msg);
    ps.executeUpdate();
  }

}

In postgres logs:

2018-06-06 10:55:38.670 CEST [70587] LOG:  execute <unnamed>: SET extra_float_digits = 3
2018-06-06 10:55:38.670 CEST [70587] LOG:  execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2018-06-06 10:55:38.689 CEST [70587] LOG:  execute <unnamed>: insert into bug_test(date, msg) VALUES($1,$2)
2018-06-06 10:55:38.689 CEST [70587] DETAIL:  parameters: $1 = '2018-06-05 23:59:59.1', $2 = 'in code: 2018-06-05T23:59:59.999999999'

and in database:

            date            |                  msg
----------------------------+----------------------------------------
 2018-06-05 23:59:59.1      | in code: 2018-06-05T23:59:59.999999999

I believe this was introduced in this PR #1119
and is caused by this calculation

int microseconds = (nanos / 1000) + (((nanos % 1000) + 500) / 1000);

There are at least two ways to fix it:

  1. Bring back previous (from 42.1.1) code, which ignores nanos
  2. Implement rounding correctly.

To compare with others, i.e. psql rounds timestamp:

insert into bug_test(date, msg) VALUES('2018-06-03 23:59:59.999999500','from psql');

produces:

           date            |                  msg
----------------------------+----------------------------------------
2018-06-04 00:00:00        | from psql

I would like to work on fixing this bug, but as I'm new to this project, please let me know what do you think about it.

@vlsi

This comment has been minimized.

Copy link
Member

vlsi commented Jun 6, 2018

I think the way to go is to add rounding before appendTime call, so it properly computes day/hour/minute/etc timestamp parts:

https://github.com/stellingsimon/pgjdbc/blob/1d610e814e33148277a7d1bf1c8a11b269d3dc4f/pgjdbc/src/main/java/org/postgresql/jdbc/TimestampUtils.java#L554

vlsi added a commit to vlsi/pgjdbc that referenced this issue Jun 6, 2018

fix: adjust date, hour, minute, second when rounding timestamp
PostgreSQL supports microsecond resolution only, so PgJDBC rounds
nanoseconds to micros. When that happens the number of seconds, minutes, etc
might change as well

fixes pgjdbc#1211
@vlsi

This comment has been minimized.

Copy link
Member

vlsi commented Jun 6, 2018

@lukeindykiewicz , would you please review #1212 ?

@lukeindykiewicz

This comment has been minimized.

Copy link
Author

lukeindykiewicz commented Jun 7, 2018

I'll try reviewing it tonight.

@lukeindykiewicz

This comment has been minimized.

Copy link
Author

lukeindykiewicz commented Jun 8, 2018

Thanks @vlsi for super fast fix!

@vlsi vlsi closed this in #1212 Jun 8, 2018

vlsi added a commit that referenced this issue Jun 8, 2018

fix: adjust date, hour, minute, second when rounding timestamp (#1212)
PostgreSQL supports microsecond resolution only, so PgJDBC rounds
nanoseconds to micros. When that happens the number of years, days, hours, seconds, minutes, etc
might change as well

fixes #1211

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

fix: adjust date, hour, minute, second when rounding timestamp (pgjdb…
…c#1212)

PostgreSQL supports microsecond resolution only, so PgJDBC rounds
nanoseconds to micros. When that happens the number of years, days, hours, seconds, minutes, etc
might change as well

fixes pgjdbc#1211

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

fix: adjust date, hour, minute, second when rounding timestamp (pgjdb…
…c#1212)

PostgreSQL supports microsecond resolution only, so PgJDBC rounds
nanoseconds to micros. When that happens the number of years, days, hours, seconds, minutes, etc
might change as well

fixes pgjdbc#1211
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.