DB timestamp (no tz) returned with local timezone in Go time.Time

[watercraft]

My current application goes though hoops to workaround the fact that the database type timestamp (without the -tz for Vertica) is returned with the local timestamp. The intention here was to record the UTC timestamp of events that may be published in different timezones. First I tried backing the local timezone out which gave me problems when we changed our daylight savings offset. What I have now converts all these timestamps to strings that are then parsed in Go. Note, the lib/pq is able to return these timestamps as UTC.

Is there some configuration I could use like the connection timezone to get these timestamps parsed into UTC?

[watercraft]

In the version I'm testing I've replaced all the occurrences of time.Local with time.UTC in column.go. The github.com/lib/pq driver seems to have more knowledge of the database types timestamp vs. timestamptz than this odbc driver.

[alexbrainman]

I am not clear what your problem is, so I assume you want to access column data types that can store time zone.

As far as I can see, none of the types currently supported by this driver allow for storing timezone information.

I googled for such types, and I can find SQL_SS_TIMESTAMPOFFSET (from https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements?view=sql-server-2017 ). According to that page, SQL_SS_TIMESTAMPOFFSET is called DatetimeOFFSET in MS SQL Server. Does your database server supports SQL_SS_TIMESTAMPOFFSET ? If it does, maybe you can adjust this source code to support columns of SQL_SS_TIMESTAMPOFFSET type.

Alex

[fineol]

I am not clear what your problem is, so I assume you want to access column data types that can store time zone.

I don't think that watercraft's problem is being unable to access column data types that can store time zone information. Instead, I think the problem is the default time zone that this driver applies to column data types that do not themselves supply time zone information.

As far as I can see, none of the types currently supported by this driver allow for storing timezone information.

Correct. And because of that, this driver must assign some time zone when creating time.Time variables. Currently it hard-codes the time zone time.Local wherever it creates time.Time variables. For example, see the Value receiver function of BaseColumn at line 179 of column.go:

    if c.SQLType == api.SQL_SS_TIME2 {
        t := (*api.SQL_SS_TIME2_STRUCT)(p)
        r := time.Date(1, time.January, 1,
            int(t.Hour), int(t.Minute), int(t.Second), int(t.Fraction),
            time.Local)
        return r, nil

Go's time.Date function requires a non-nil *Location for the final argument, and time.Local is a reasonable choice to use when you don't have any other information. However, it isn't the only choice, and it won't always be the right choice.

For example, assume you have a system that uses Microsoft SQL Server as the database engine, and all date/time information is stored in DateTime2 columns. Further assume that the system architect declared that, by convention, all data stored in these columns must be UTC at the time of insertion.

SQL Server's DateTime2 data type does not store time zone information, so a value in a table might look something like this:

2021-05-25 15:54:38.813

Because of the system architect's convention that all date/time values are inserted as UTC values, that value should be interpreted as:

2021-05-25 15:54:38.813 +0000 UTC

But when this driver extracts the value it will assign it to the local time zone. If the system on which this driver is running is in the EDT time zone, for example, then this driver will return a time.Time variable with value:

2021-05-25 15:54:38.813 -0400 EDT

That is four hours off what it was intended to be.

Unfortunately, Go doesn't have a built-in method to rezone a time value that has the wrong time zone. You can convert to UTC (e.g. val.In(time.UTC)), but that just confirms that the above value is four hours off what it should be (19:54 vs 15:54):

2021-05-25 19:54:38.813 +0000 UTC

Therefore, it would be handy if the default time zone for this driver could be configured rather than hard coded as time.Local. Ideally it would be configured on a connection basis, perhaps with an extension to the connection string, but that may violate ODBC connection string standards. In addition, work would have to be done to push that setting down to the BaseColumn struct so it is available in the Value function.

Does this make sense, and would you have any interest in such a change?

[alexbrainman]

Does this make sense, and would you have any interest in such a change?

Replied at https://github.com/alexbrainman/odbc/issues/157#issuecomment-849461242

Alex