B4J Question Issue retrieving SQL Server DateTime2 column at change of clocks (GMT to BST)

[Chris2]

I think I've found an issue in the MS JDBC driver similar but slightly different to that discussed previously in relation to jRDC2 here.
This time I came across a problem when querying an MS SQL Server database DateTime2 column directly with an SQL object (via jConnectionPool).
I think I've solved the problem (based on the help from @OliverA in the jRDC2 post linked above), but I would appreciate it if someone with access to SQL Server would check that it's not just me making mistakes or going mad.
Thanks in advance!

I'm in the UK and our clocks moved forward on Sunday 27/03/2022 from GMT to BST.
The problem I noted was when trying to retrieve a datetime (yyyy-MM-dd HH:mm:ss) from a datetime2 column.

Querying in Server Management Studio gives:

But the code below logs 2022-03-27 02:00:00

#AdditionalJar: mssql-jdbc-9.2.1.jre11    'Ive tried mssql-jdbc-10.2.0.jre11 as well, same result.
DateTime.SetTimeZone(0)
Private Sub btnTest_Click
    
    pool.GetConnectionAsync("Con")
    Wait For Con_ConnectionReady (success As Boolean, sql As SQL)
    If success Then
        
        Dim query As String = $"SELECT reset_period_start FROM loggers WHERE logger_unique_id=?"$
        Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", query, Array(1))
        Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, rs As ResultSet)
    
        If success Then
            
            Do While rs.NextRow
                If rs.GetString("reset_period_start")<>Null Then
                    Log(rs.GetString("reset_period_start")) '2022-03-27 02:00:00
                End If
                
            Loop
            rs.Close
        End If
        
        sql.Close
    End If
End Sub

What is different to the jRDC2 issue post, and what really puzzles me is that if I change the time in the database to 2022-03-27 03:00:00, so it's still after the BST change, both SSMS & the code above correctly return 2022-03-27 03:00:00.
It seems to be only times between 2022-03-27 01:00:00 & 2022-03-27 01:59:59 that are affected.

Can anyone verify this, &/or explain it?

Thanks again in advance!

Spoiler: How I've solved it

DateTime.SetTimeZone(0)

Private Sub btnTest_Click
    
    pool.GetConnectionAsync("Con")
    Wait For Con_ConnectionReady (success As Boolean, sql As SQL)
    If success Then
        
        Dim query As String = $"SELECT reset_period_start FROM loggers WHERE logger_unique_id=?"$
        Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", query, Array(1))
        Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, rs As ResultSet)
    
        If success Then
            Dim jrs As JavaObject = rs
            
            Do While rs.NextRow
                If rs.GetString("reset_period_start")<>Null Then
                    Dim strPerStart As String = DBHelper.GetUTCDateTimeColumn(jrs, 1)    'the number here is the column index (1 based) of the results
                    Log(strPerStart)
                End If
                
            Loop
            rs.Close
        End If
        
        sql.Close
    End If
End Sub


Public Sub GetUTCDateTimeColumn (jrs As JavaObject, colNum As Int) As String
    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"))
    Dim SQLTime As JavaObject = jrs.RunMethodJO("getTimestamp", Array(colNum, cal.RunMethod("getInstance", Array(tzUTC))))
    
    If SQLTime.IsInitialized Then
        
        Dim longLastBlockDT As Long=SQLTime.RunMethod("getTime", Null)
        Log("getTime=" & longLastBlockDT)
        DateTime.DateFormat="yyyy-MM-dd HH:mm:ss"
        Return DateTime.Date(longLastBlockDT)
        
    Else
        Return Null
    End If
End Sub

 

[MicroDrie]

The trouble is, if you go back an hour at the first time moment when daylight saving time starts, you don't have to go back an hour at the second time when you return at the same time value. As a result, two identical date and time formats still represent two different "real" time moments between a time representation between 2022-03-27 01:00:00 & 2022-03-27 01:59:59.

Every software programmer makes a choice how to deal with this. An example is to choose a reference time for recording that does not use summer and winter time and then only take this into account with the local time at the moment that time must presented on screen or paper.

 

[Chris2]

An example is to choose a reference time for recording that does not use summer and winter time and then only take this into account with the local time at the moment that time must presented on screen or paper.

That's exactly what I'm doing. All the datetimes stored in the database are UTC/GMT, and hence are consistent all year.
The problem I identified here was that the query returned a datetime string that had been automatically shifted to local time (BST in this case). It did not return the value that is actually in the database table.

 

[MicroDrie]

You may find a solution to your query problem at SQL Server JDBC Driver Date/Timezone Related Query

 

[Chris2]

You may find a solution to your query problem at SQL Server JDBC Driver Date/Timezone Related Query

Thanks for your response. However, as I mentioned in my original post I have already solved the problem I think (see the Spoiler section).
I wasn't asking for a solution, rather for someone with access to SQL Server to confirm/verify the issue.