Timestamp with Local Time Zone conversion

[rocksnow1942]

My datatabase has DBTIMEZONE set to -08:00, and the timestamp columns are stored with TIMESTAMP WITH LOCAL TIME ZONE.

When reading the timestamp, it doesn't convert correctly with respect to the DBTIMEZONE.

Is there a way to handle the timezone conversion?

[sijms]

this is the code that decode timestamp with local time zone

case TimeStampeLTZ, TimeStampLTZ_DTY:
tempTime, err := converters.DecodeDate(par.BValue)
if err != nil {
    return err
}
par.oPrimValue = tempTime
if conn.dbTimeLoc != nil && conn.dbTimeLoc != time.UTC {
    par.oPrimValue = tempTime.In(conn.dbTimeLoc)
}

[sijms]

would you give me an example for input and output to test

[rocksnow1942]

I used similar code in https://github.com/sijms/go-ora/blob/master/examples/time_issue/main.go

func main() {
    var tz string
    err := oraClient.QueryRow("SELECT DBTIMEZONE FROM DUAL").Scan(&tz)
    if err != nil {
        fmt.Printf("error in Query: %v\n", err)
    }
    fmt.Printf("DBTIMEZONE: %v\n", tz)

    // test timezone
    var t time.Time
    err = oraClient.QueryRow("SELECT SYSTIMESTAMP FROM DUAL").Scan(&t)
    if err != nil {
        fmt.Printf("error in Query: %v\n", err)
    }
    fmt.Printf("SYSTIMESTAMP location: %s, time: %v\n", t.Location(), t)

    current := time.Now()
    fmt.Printf("current go time: %v\n", current)

    createTable(oraClient)
    defer dropTable(oraClient)
    insert(oraClient)
    query(oraClient)
}

Here is the output:

DBTIMEZONE: -08:00
SYSTIMESTAMP location: UTC, time: 2024-04-25 17:32:38.357101 +0000 UTC
current go time: 2024-04-25 17:32:38.357397442 +0000 UTC m=+0.236532246
time in Asia/Shanghai:  2024-04-26 01:32:38.365966049 +0800 CST
query with sql: 
DATE:  2024-04-25 17:32:38 +0000 UTC
Timestamp:  2024-04-25 17:32:38.365992 +0000 UTC
Timestamp TZ:  2024-04-26 01:32:38.365992 +0800 CST
Timestamp with local TZ:  2024-04-25 09:32:38.365992 +0000 UTC

The "Timestamp with local TZ" is read as: 2024-04-25 09:32:38.365992 +0000 UTC, which is incorrect.

[rocksnow1942]

From my understanding, oracle has 3 time zones, DBTIMEZONE affects TIMESTAMP WITH LOCAL TIME ZONE.

I think the name of function Connection.getDBTimeZone is a little misleading:

func (conn *Connection) getDBTimeZone() {
    var current time.Time
    err := conn.QueryRowContext(context.Background(), "SELECT SYSTIMESTAMP FROM DUAL", nil).Scan(&current)
    if err != nil {
        conn.dbTimeLoc = time.UTC
    }
    conn.dbTimeLoc = current.Location()
    // conn.dbTimeLoc = time.FixedZone("UTC-8", -8*60*60)
    fmt.Printf("dbTimeLoc: %v\n", conn.dbTimeLoc)
}

this function actually gets the server's OS time zone (In my case, it's UTC), not the DBTIMEZONE.

Oracle stores TIMESTAMP WITH LOCAL TIME ZONE as values without timezone info, offset by the DBTIMEZONE. (this is set to -08:00 in my case). To get the correct timestamp, it need to be offset with DBTIMEZONE. oracle-database/19/sqlrf/DBTIMEZONE

So may be we can add a new Connection.dbTimeZone to indicate the DBTIMEZONE, and modify Connection.getDBTimeZone to query and set this value.

Then in parameter.go we can do:

case TimeStampeLTZ, TimeStampLTZ_DTY:
    tempTime, err := converters.DecodeDate(par.BValue)
    if err != nil {
        return err
    }
    par.oPrimValue = tempTime
    if conn.dbTimeZone != nil && conn.dbTimeZone != time.UTC {
        par.oPrimValue = time.Date(tempTime.Year(), tempTime.Month(), tempTime.Day(),
        tempTime.Hour(), tempTime.Minute(), tempTime.Second(), tempTime.Nanosecond(), conn.dbTimeZone)
    }

[sijms]

I update the code in last commit to get correct database timezone would you please test and feedback

[sijms]

fixed 2.8.14

[rocksnow1942]

Thanks for the quick fix! However, I think the implementation is incorrect:

Here is the result from the same code:

DBTIMEZONE: -08:00
SYSTIMESTAMP location: UTC, time: 2024-04-26 16:23:15.66096 +0000 UTC
current go time: 2024-04-26 16:23:15.661276771 +0000 UTC m=+0.229620602
time in Asia/Shanghai:  2024-04-27 00:23:15.67199898 +0800 CST
query with sql: 
DATE:  2024-04-26 16:23:15 -0800 -08:00 # should be 2024-04-26 08:23:15 -0800 -08:00
Timestamp:  2024-04-26 16:23:15.672049 -0800 -08:00 # should be 2024-04-26 08:23:15.672049 -0800 -08:00
Timestamp TZ:  2024-04-27 00:23:15.672049 +0800 CST
Timestamp with local TZ:  2024-04-26 00:23:15.672049 -0800 -08:00 # should be 2024-04-26 08:23:15.672049 -0800 -08:00

I think this is what happened:	Step	Value
current time in UTC	2024-04-26 16:23:15 +0000
save to TIMESTAMP WITH LOCAL TIME ZONE	2024-04-26 08:23:15 (no timezone)
read to tempTime	2024-04-26 08:23:15 +0000
in case TimeStampeLTZ, TimeStampLTZ_DTY	2024-04-26 00:23:15 -0800

This PR should fix it: https://github.com/sijms/go-ora/pull/550 Please take a look, thanks!

[rocksnow1942]

The same test with fix https://github.com/sijms/go-ora/pull/550 :

DBTIMEZONE: -08:00
SYSTIMESTAMP location: UTC, time: 2024-04-26 17:22:40.187117 +0000 UTC
current go time: 2024-04-26 17:22:40.187535577 +0000 UTC m=+0.236271882
time in Asia/Shanghai:  2024-04-27 01:22:40.198444698 +0800 CST
query with sql: 
DATE:  2024-04-26 09:22:40 -0800 -08:00
Timestamp:  2024-04-26 09:22:40.198474 -0800 -08:00
Timestamp TZ:  2024-04-27 01:22:40.198474 +0800 CST
Timestamp with local TZ:  2024-04-26 09:22:40.198474 -0800 -08:00