Date changes while extracting from Database

Question

I have a database (MS SQL) with a table "PositionMessages" with the columns ( ID as (int), issi as (varchar), Longitude as (varchar), Latitude as (varchar), Received_at as (datetime2))

The table is filled by a running jar on a server. Works fine. Database entry looks like this:

ID      |issi           |Longitude           |Latitude           |Received_at 
301208  |6683904        |15,4464340209961    |46,9848775863647   |2017-07-25 06:37:21

The datetime is well set in UTC Format but when i load data into my webinterface of my project the datetime just adds 2 days and i didn't figured out why.

The SQL query looks like this: SELECT * FROM PositionMessages

In java i got the fileds via jdbc driver:

(com.microsoft.sqlserver.jdbc.SQLServerDriver)

tempTS = result.getTimestamp("received_at");

try {
    result = source.executeStatement(query);
    while (result.next()) {
        tempID = result.getString("issi");
        tempLong = result.getString("longitude");
        tempLat = result.getString("latitude");
        tempTS = result.getTimestamp("received_at");
        tempLong = tempLong.replace(',', '.');
        tempLat = tempLat.replace(',', '.');
        route.add(new TetraDataset(tempID, tempLat, tempLong, tempTS));
    }
    return route;
} catch (SQLException e) {
    System.err.println("ERROR WHILE READING THE ROUTE DATA FROM DB");
    return null;
}

So, if i load the entry 2017-07-25 06:37:21 it looks like so in java tempTS 2017-07-27 06:37:21

Did someone had the same error?

Thanks for help!

well it could be a display problem or problem with `TetraDataset` but first debug it and see what the value is after your do `tempTS = result.getTimestamp("received_at");` — Scary Wombat, Jul 25 '17 at 07:05
I porpmted tempTS right below tempTS = result.getTimestamp("received_at"); and it displayed it with the two extra days — Thomas Höller, Jul 25 '17 at 07:07
well that is the data in your DB then - maybe you have duplicate data or using a different DB or something is cached? — Scary Wombat, Jul 25 '17 at 07:10
A long, long time ago I had a similar problem, and it had to do with different base dates used by different software and it was two days difference and did involve SQL Server. I am going through old code trying to find where I made the two day adjustment. It was consistently two days out. If I find it, I'll write again. — Jonathan Willcock, Jul 25 '17 at 07:13
My memory isn’t clear, but I think in a similar situation I suggested to an asker to change to the modern Java date & time API, and when s/he did that, the problem was no longer there. — Ole V.V., Jul 25 '17 at 07:26
Something like `tempInstant = result.getObject("received_at", Instant.class);`. Requires Java 8 and a JDBC 4.2 compliant JDBC driver. — Ole V.V., Jul 25 '17 at 07:37
see https://stackoverflow.com/questions/43635787/cast-vs-ssis-data-flow-implicit-conversion-difference I had similar problem. — avb, Jul 25 '17 at 08:45

score 1 · Accepted Answer · answered Jul 25 '17 at 08:56

1

I looked again into my source table and the datatype of recieved_at was datetime2. I changed it to datetime, and now it displays the correct date and time!

Thanks for your help and your suggestions!

answered Jul 25 '17 at 08:56

Thomas Höller

11
4

Thanks for reporting your solution. I stand by my suggestion to see if you can use `java.time` rather than the outdated `Timestamp` class. The modern API is generally more convenient to work with, and with a modern JDBC driver you will no longer need to convert to and from some `java.sql` type since the JDBC driver will give you `java.time` types directly, – Ole V.V. Jul 25 '17 at 10:27

Date changes while extracting from Database

1 Answers1