1

There is a database procedure (to which I do not have access to, so I can't view its source or edit it) in a Sybase database that I query for information about events, and those events have a start and end date.

EDIT: This effect also happens when I queried a freshly-created table of just dates, created as such:

create table jvo_test.test_dates(dt datetime not null primary key)
insert into jvo_test.test_dates(dt) select  dateadd(mi, (id-1) *30, '2019-03-06 00:00:00') from rle.row_generator where id between 1 and 48

END EDIT

The trouble is that when these events take place an hour after midnight (so in the 00:00 - 01:00 range) the resulting timestamp jumps back in past an hour???

Example: right now the procedure returns two events, one on 23:44 and the other for 00:07

Code:

Connection c = ds.getConnection();
String sql = "procedure_name 'param1', 'param2', 'param3'";
PreparedStatement ps = c.prepareStatement(sql);

ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
    for(int i = 1; i < rsmd.getColumnCount(); i++) {
        log.debug("{} is of type: {}", rsmd.getColumnName(i), rsmd.getColumnType(i));
    }
    log.debug("AS STRING start_time: {}, end_time: {}",
        rs.getString("start_time"),rs.getString("end_time"));
    log.debug("AS TIMESTAMP start_time: {}, end_time: {}",
        rs.getTimestamp("start_time"),rs.getTimestamp("end_time"));
}

This results in the following log lines

start_time is of type: 93
end_time is of type: 93
AS STRING start_time: 2019-02-19 23:44:23.000000, end_time: 2019-02-19 23:44:29.000000
AS TIMESTAMP start_time: 2019-02-19 23:44:23.0, end_time: 2019-02-19 23:44:29.0

start_time is of type: 93
end_time is of type: 93
AS STRING start_time: 2019-02-20 00:0-53:46.000000, end_time: 2019-02-20 00:0-53:53.000000
AS TIMESTAMP start_time: 2019-02-19 23:07:46.0, end_time: 2019-02-19 23:07:53.0

start_time and end_time are both of type 93 or Timestamp.

Notice how the second event, which should be starting on 00:07, has jumped an hour into the past to 23:07

Where am I going wrong to have this one-hour offset?

Karbik
  • 41
  • 6
  • 1
    Why does it say "-53:46" and isn't midnight - 53 = 07? – Joakim Danielson Mar 08 '19 at 12:50
  • That is a splendid question Joakim and one I wouldn't mind finding out myself either – Karbik Mar 08 '19 at 12:56
  • I assume the procedure returns start_time & end_time as a string? What is the type in the db and the original value? – Joakim Danielson Mar 08 '19 at 13:18
  • Procedure returns a timestamp. And sadly I don't have access to the database itself to see what the values are there, I can only use the procedure – Karbik Mar 08 '19 at 13:21
  • 1
    Reading [this question](https://stackoverflow.com/questions/3500703/what-is-the-mysterious-timestamp-datatype-in-sybase) and specially the approved answer I am pretty sure you can't use `rs.getTimestamp()` – Joakim Danielson Mar 08 '19 at 13:28
  • 1
    Or maybe your jdbc driver is actually supporting this type but the issue has to do with Locale and/or time zone settings? – Joakim Danielson Mar 08 '19 at 13:35
  • Possible explanations include: (1) The stored procedure was coded to move those timestamps one hour back into the previous day. (2) There is a summer time transition or other anomaly on Feb 20 that causes the hour between 00 and 01 not to exist in the time zone in question. All guesswork, though. – Ole V.V. Mar 09 '19 at 07:19
  • The `Timestamp` class is poorly designed and long outdated. You should use either `rs.getObject("start_time", Instant.class)` or `rs.getObject("start_time", LocalDateTime.class)`. You may also see if it does something to your issue, though I wouldn’t set my hopes high. – Ole V.V. Mar 09 '19 at 07:23
  • According to [Daylight Saving Time Around the World 2019](https://www.timeanddate.com/time/dst/2019.html) nowhere in the world does summer time (DST) begin in February. I thought I had a good guess, but it appears it was a wrong guess anyway. – Ole V.V. Mar 09 '19 at 07:26
  • @OleV.V. Regrettably, it seems that getObject is not part of Sybase's driver's capabilities, I received an runtime error: `java.lang.AbstractMethodError: Method com/sybase/jdbc4/jdbc/SybResultSet.getObject(Ljava/lang/String;Ljava/lang/Class;)Ljava/lang/Object; is abstract` Regarding your other point about summertime transistion, sadly this is not a problem limited to just this set of dates, it happens for every day I've been able to test with. – Karbik Mar 10 '19 at 15:19
  • 1
    Sorry. It would seem that your JDBC driver is too old for what I was suggesting ([source](https://stackoverflow.com/questions/34984853/abstractmethoderror-on-resultset-getobject)). In case you want to upgrade, [this might be relevant](https://answers.sap.com/questions/155909/jconnect-jdbc-41-or-42-driver.html). Passing `Instant.class` or `LocalDateTime.class` to `getObject` requires JDBC 4.2. – Ole V.V. Mar 10 '19 at 17:52

1 Answers1

3

Problem was resolved by updating the JDBC database driver from build 26502 to 27361

To find out the version of your driver, do

java -jar jconn4.jar

Old version output this as the first row:

jConnect (TM) for JDBC(TM)/7.00(Build 26502)/P/EBF17993/JDK16/Thu Jun  3  3:09:09 2010

New version output this:

jConnect (TM) for JDBC(TM)/7.07 SP139 (Build 27361)/P/EBF27161/JDK 1.6.0/jdbcmain/OPT/Thu Jul 27 02:39:00 PDT 2017
Karbik
  • 41
  • 6