B4J Question jRDC2, retrieve SQL Server DateTime2 as UTC

[Chris2]

I have UTC date/times stored in a SQL Server database datetime2(0) column. They are written as a string in the format 'yyyy-MM-dd HH:mm:ss'.
I'm in the UK so until last Sunday our clock time was UTC/GMT, but it's now BST (UTC/GMT + 01:00)

Since the local clock change, when I query the database jRDC2 seems to be assuming that the stored datetime is in my local time and returns ticks which are an hour before the stored time.
So for example;
In the database I have 2021-03-28 02:00:00,
Querying via JRDC2 returns 1616893200000, which is 2021-03-28 01:00:00 UTC.

Is there a way I can have JRDC2 treat the stored date/times as UTC?

I've tried adding

DateTime.SetTimeZone(0)

into jRDC2 but get the same result.

Thanks.

 

[marcick]

Maybe there are smarter solutions, but I have this couple of subs to manage localtime.

Convert the date in tick with Datetime.Dateparse, then apply or remove the localtime correction.
Then convert again ticks in datetime

Sub GetGreenwich(t As Long) As Long
    Dim tz As Double
    tz=DateTime.GetTimeZoneOffsetAt(t)
    Return t-(tz*3600000)
End Sub

Sub GetLocalTime(t As Long) As Long
    Dim tz As Double
    tz=DateTime.GetTimeZoneOffsetAt(t)
    Return t+(tz*3600000)
End Sub

 

[Chris2]

Thanks @marcick. I realise that I can always convert the ticks I'm getting from jRDC2.

The particular app I'm working on uses UTC/GMT throughout though so I was hoping that I could somehow get jRDC2 to ignore the PC/server timezone completely and assume all date/times it retrieves are UTC.

 

[OliverA]

This is odd. From my understanding, time stored in a database w/o any offset information is treated as UTC by JDBC. Are you sure that there is nothing in the DB configuration that changes the time returned? Have you tried querying the time with SQL Studio to compare the results? Could try adding a log statement to jRDC2 to see what is going on

            Else If DateTimeMethods.ContainsKey(ct) Then
                Log(jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If
            Else

 

[Chris2]

I thought it was a bit strange too.
I'm not sure what could be set in the database config that could cause this, but I'll poke around.

I've queried the whole table within SQL Management Studio and get the expected datetime, but not this column by itself.
I'll try that also, and stick your suggested log into JRDC2.
Thanks.

 

[Chris2]

So, querying just this column in SQL Management Studio gives me the correct/expected result:

I added your suggested log @OliverA, along with another;

Else If DateTimeMethods.ContainsKey(ct) Then
                Log(jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                    Log(row(i))          ' Added log of end result for datetime ticks
                Else
                    row(i) = Null
                End If
            Else

When I query the database the log in jRDC2 is;

2021-03-28 02:00:00.0
1616893200000
2021-04-01 16:50:29 Command: query: select_lastblockDT, took: 705ms, client=127.0.0.1

Just for fun, I changed my PC time zone to '(UTC+04:00) Tbilisi', and queried the DB again, and got:

2021-03-28 02:00:00.0
1616882400000

To me this suggests that the "getObject" method is retrieving the DateTime from the database 'as is', but that the conversion to ticks is assuming that the string is local time.
Am I mis-understanding something here?

 

[OliverA]

Log SQLTime. This should let us know if it is the getTimestamp of the ResultSet object or the getTime method of the Timestamp object that is giving us fits
Update: Looking at your other post, it looks like it is getTimestamp. Run your tests anyways, just to confirm.
Update2: If it is getTimestamp, then your other method will not work (using MS SQL's DateTimeOffset), since DateTimeOffset's getTimestamp cannot be influenced. ResultSet's can (that would require another mod to jRDC2 code)

 

[Chris2]

Else If DateTimeMethods.ContainsKey(ct) Then
                Log("ct=" & ct)
                Log(jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    Log("SQLTime=" & SQLTime)
                    row(i) = SQLTime.RunMethod("getTime", Null)
                    Log(row(i)) ' Added log of end result for datetime ticks
                Else
                    row(i) = Null
                End If

gives us;

ct=93
2021-03-28 02:00:00.0
SQLTime=(Timestamp) 2021-03-28 02:00:00.0
1616893200000

Which I think means that getTimestamp is being inconsistent doesn't it?
It seems to be converting to local time when used with DateTimeOffset as on my other post, but leaving it as it is here with a DateTime2 column.
getTime is then taking SQLTime to be local time in both cases and adjusting it to what it thinks is UTC to return the ticks?

The end result being (I think) that I'm getting the correct/expected ticks value from DateTimeOffset, but not DateTime2.

 

[Chris2]

I'm a bit out of my depth here but if I'm understanding the following link correctly, I think this may be a known issue...

Handling of datetime is flawed · Issue #339 · microsoft/mssql-jdbc

The way that datetime is handled by mssql-jdbc is flawed. datetime types are time-zoneless, however, the driver maps them to the JVM time zone when deserializing the datetime sent from SQLServer. T...

github.com

"The way that datetime is handled by mssql-jdbc is flawed. datetime types are time-zoneless, however, the driver maps them to the JVM time zone when deserializing the datetime sent from SQLServer."

...with a possible solution using LocalDateTime...

add support for getObject(n, java.time.LocalDateTime.class) · Issue #744 · microsoft/mssql-jdbc

(Continuation of #339. Also prompted by this Stack Overflow question.) As of mssql-jdbc-6.5.4.jre8-preview.jar the driver still does not support the following statement when retrieving a value from...

github.com

Add support for LocalDate, LocalTime and LocalDateTime to be passed as 'type' in ResultSet.getObject() by gordthompson · Pull Request #749 · microsoft/mssql-jdbc

Issue #744 - support for rs.getObject(n, java.time.LocalDateTime.class)

github.com

?

 

[OliverA]

Which I think means that getTimestamp is being inconsistent doesn't it?

The question now is why. My (non-expert) understanding is that a string timestamp without timezone information should be treated UTC/GMT. Looks like the Microsoft JDBC driver is not doing this, or my understanding of how JDBC treats string timestamps is faulty. getTime does no conversion, it only gives you the value of the Timestamp object. Timestamp's toString does do a conversion (using your system's local timezone). So even though you see

SQLTime=(Timestamp) 2021-03-28 02:00:00.0

the internal time is

1616893200000

And it looks like I've been Ninja'd and it is Microsoft's JDBC driver that's out of spec

 

[OliverA]

...with a possible solution using LocalDateTime...

Actually, why don't you try this

            Else If DateTimeMethods.ContainsKey(ct) Then
                If ct = 93 Then
                    Dim tz As JavaObject
                    tz.InitializeStatic("java.util.TimeZone")
                    Dim cal As JavaObject
                    cal.InitializeStatic("java.util.Calendar")
                    Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1, tz.RunMethod("getTimeZone", Array ("UTC"))))
                Else
                    Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))                   
                End If.
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If
            Else
                row(i) = jrs.RunMethod("getObject", Array(i + 1))
            End If

I had this code ready yesterday just in case we still had issues with Datetime2 and Timestamps. Similar code seems to be posted in the third link you provided (Issue#744).
Update: May want to add some log statements to see what is going on

 

[Chris2]

My (non-expert) understanding is that a string timestamp without timezone information should be treated UTC/GMT

That's what I assumed would be the case, and what would make more sense to me. Anyway....

Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1, tz.RunMethod("getTimeZone", Array ("UTC"))))

is giving:

(RuntimeException) java.lang.RuntimeException: Method: getTimestamp not matched.

But, based on your pointers and the code on that github issue #749, I've changed it to

If ct = 93 Then    'https://www.b4x.com/android/forum/threads/jrdc2-retrieve-sql-server-datetime2-as-utc.129255/
          Log(jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
          Dim tz As JavaObject
          tz.InitializeStatic("java.util.TimeZone")
          Dim cal As JavaObject
          cal.InitializeStatic("java.util.Calendar")
          Dim tzUTC As JavaObject = tz.RunMethod("getTimeZone", Array ("UTC"))
          Log("tzUTC=" & tzUTC)
          Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1, cal.RunMethod("getInstance", Array(tzUTC))))
Else
          Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
End If
If SQLTime.IsInitialized Then
          Log("SQLTime=" & SQLTime)    ' Timestamp's toString does do a conversion (using your system's local timezone) 
          row(i) = SQLTime.RunMethod("getTime", Null)
Else
          row(i) = Null
End If
Log(row(i)) ' Added log of end result for datetime ticks

2021-03-28 02:00:00.0
tzUTC=(ZoneInfo) sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]
SQLTime=(Timestamp) 2021-03-28 03:00:00.0
1616896800000     '= 2021-03-28 02:00:00 UTC :)!

I've only tested this very quicky, but it seems to work!
Thanks for all your help with this.

 

[OliverA]

Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1, tz.RunMethod("getTimeZone", Array ("UTC"))))

(RuntimeException) java.lang.RuntimeException: Method: getTimestamp not matched.

Yeah, that's what I get for copying and pasting. My B4J console app test code was

    Dim tz As JavaObject
    tz.InitializeStatic("java.util.TimeZone")
    Log(tz.RunMethod("getTimeZone", Array ("UTC")))
    Dim cal As JavaObject
    cal.InitializeStatic("java.util.Calendar")
    cal.RunMethod("getInstance", Array(tz.RunMethod("getTimeZone", Array ("UTC"))))

My plan was to copy the whole last line over to the jrs.RunMethodJO. Luckily you were able to figure it out.

I've only tested this very quicky, but it seems to work!

It should. This overwrites the default getTimestamp method, forcing it to calculate the incoming (from the DB) date as UTC. I'm glad you figured out my mistake (I make lots of them). It's amazing what one has to deal with when it comes to implementation bugs. And this (the implementation bug) is not coming from a small company, but from a multi-billion dollar juggernaut. I guess we're all humans