Incorrect date time value returned about odbc HOT 3 OPEN

@fineol thank you for creating this issue.

Describe the bug

I don't consider this a bug.

Since Microsoft SQL server does not store timezone in dates, I had to put some value into time.Time timezone when reading SQL Server data.

The only choices I had were time.Local and time.UTC. Both of these choices are bad. I picked time.Local.

For example, I have both UTC and local time columns in one of my databases. So I have to convert them when reading.

But I agree with you, some people might get away with just having time.UTC, instead of time.Local in this package. Perhaps something like you suggested - #117 (comment)

Maybe we can add new Driver.Loc field

type Driver struct {
        Stats
        Loc *time.Location    // Location used for all time.Time values

        // Has unexported fields.
}

with Loc set to time.Local so we don't break any existing users.

But I don't need this feature. So you have to implement it yourself. And the change will need some test to verify that change actually work. I will accept a PR, if you want to implement this.

Thank you.

Alex

from odbc.

Yes, many database date-time types such as SQL Server's datetime and datetime2 do not store time zone information. And yes, Go forces us to supply a location when creating a time.Time variable. Therefore, we have to pick something, and time.Local and time.UTC are the two most readily usable choices. Neither are ideal for all circumstances, but I wouldn't go so far as to say both are bad.

However, I still contend that use of time.Local creates a bug in some circumstances. It may not show up at all for some people due to the time zones they operate in. And even for those who are susceptible, it may not show up in practice due to the relative rarity of the problematic dates. But it is real, and I will try to demonstrate more clearly.

You mentioned that you have both UTC and local time columns in one of your databases. Let's concentrate on one of the UTC columns and assume that it contains the values 2021-03-14 01:30:00 and 2021-03-14 02:30:00. I'll simulate a query of this data with the following T-SQL code:

declare @utc1 datetime, @utc2 datetime;
set @utc1 = '2021-03-14 01:30:00';
set @utc2 = '2021-03-14 02:30:00';
select @utc1 union all select @utc2;

When you run the above in SQL Server Management Studio, you get the following results, as expected:

2021-03-14 01:30:00.000
2021-03-14 02:30:00.000

Next, let me revise my test code to extract all the rows from the result set. I'll also wrap it in a function that accepts a time zone location string to make it easy to simulate different local times:

func DemoBug(db *sql.DB, tzs string) {

	tz, err := time.LoadLocation(tzs)
	if err != nil {
		log.Fatalf("Error loading time zone: %v", err)
	}
	time.Local = tz
	r, err := db.Query("declare @utc1 datetime, @utc2 datetime;" +
		"set @utc1 = '2021-03-14 01:30:00';" +
		"set @utc2 = '2021-03-14 02:30:00';" +
		"select @utc1 union all select @utc2;")
	if err != nil {
		log.Fatalf("Error executing database query: %v", err)
	}
	var t time.Time
	for r.Next() {
		if err := r.Scan(&t); err != nil {
			log.Fatalf("Error scanning results: %v", err)
		}
		fmt.Println(t)
	}
	r.Close()
}

If I run the code with the local time zone set to UTC, I get the correct results exactly:

DemoBug(db, "UTC")

2021-03-14 01:30:00 +0000 UTC
2021-03-14 02:30:00 +0000 UTC

If I run the code in a local time zone that does not implement daylight savings, such as Perth, Australia, I get the wrong time zone, but the time components are correct and, as you indicate you do in your code, I could convert them to the right time zone if desired:

DemoBug(db, "Australia/Perth")

2021-03-14 01:30:00 +0800 AWST
2021-03-14 02:30:00 +0800 AWST

However, if I run the code in a local time zone that does implement daylight savings, such as New York, USA, I get not only the wrong time zone, but I also get the wrong hour in the second row:

DemoBug(db, "America/New_York")

2021-03-14 01:30:00 -0500 EST
2021-03-14 01:30:00 -0500 EST

That second row is where the bug reveals itself. The value is off by an hour. If you inspect the private fields of the time.Time struct for both values, you will see that they are identical. Both have wall = 0 and ext = 63751300200. Thus, there is no way to distinguish them and convert the second to the correct value.

For completeness, here is a variant that rezones the values to demonstrate that changing the time zones of the returned incorrect value doesn't fix the problem:

func Rezone(t time.Time, loc *time.Location) time.Time {

	y, o, d := t.Date()
	h := t.Hour()
	m := t.Minute()
	s := t.Second()
	n := t.Nanosecond()

	return time.Date(y, o, d, h, m, s, n, loc)
}

func DemoBugWithRezone(db *sql.DB, tzs string) {

	tz, err := time.LoadLocation(tzs)
	if err != nil {
		log.Fatalf("Error loading time zone: %v", err)
	}
	time.Local = tz
	r, err := db.Query("declare @utc1 datetime, @utc2 datetime;" +
		"set @utc1 = '2021-03-14 01:30:00';" +
		"set @utc2 = '2021-03-14 02:30:00';" +
		"select @utc1 union all select @utc2;")
	if err != nil {
		log.Fatalf("Error executing database query: %v", err)
	}
	var t time.Time
	for r.Next() {
		if err := r.Scan(&t); err != nil {
			log.Fatalf("Error scanning results: %v", err)
		}
		fmt.Println(Rezone(t, time.UTC))
	}
	r.Close()
}

With rezoning, the code works perfectly when you are in a non-daylight savings area such as Perth. Both the time and the location are as desired:

DemoBugWithRezone(db, "Australia/Perth")

2021-03-14 01:30:00 +0000 UTC
2021-03-14 02:30:00 +0000 UTC

But the user in New York still has problems:

DemoBugWithRezone(db, "America/New_York")

2021-03-14 01:30:00 +0000 UTC
2021-03-14 01:30:00 +0000 UTC

Maybe we can add new Driver.Loc field

That would be the simplest solution with the least impact to the codebase. It isn't as flexible as making the choice configurable at the connection level as NickTaylor and I suggested, but it might be the best first step.

But I don't need this feature. So you have to implement it yourself. And the change will need some test to verify that change actually work. I will accept a PR, if you want to implement this.

Understood, and I appreciate your willingness to accept a PR. At this point I am not likely to use this driver for the project I was considering, so if I am able to work on it, it will have to be on my own time.

from odbc.

At this point I am not likely to use this driver for the project I was considering, ...

Then this is not a problem for you.

Alex

from odbc.