Client timezone is not honored?

[btnguyen2k]

Server:

• Oracle Express v21.3.0-xe (running as container)
• Timezone: +11:00
• Table column type: TIMESTAMP(0) WITH TIME ZONE

Client:

• Go v1.21.4
• On same machine, timezone +11:00
• Input value of type time.Time with value specifically set to 2021-02-28 23:24:25 +0430 (timezone Asia/Kabul).
• github.com/sijms/go-ora/v2 v2.7.24 used as database/sql driver.

Row inserted without error. However, when I queried back the row (with SELECT statement), I got value of 2021-02-28 23:24:25 +1100. I would expect the returned value would be 2021-02-28 23:24:25 +0430.

Is this a bug or is there any setting that I have missed?

Edit: Queried directly from sqlplus:

SQL> SELECT * FROM test_timezone;

ID       DATA_DATETIMEZ
-------- ---------
001      28-FEB-21 11.24.25 PM +11:00

[sijms]

oracle has 3 type of time object DATE, timestamp, timestamp with timezone go has only one type for time.Time

go type	oracle type
time.Time	DATE
go_ora.TimeStamp	TIMESTAMP
go_ora.TimeStampTZ	TIMESTAMP WITH TIME ZONE

so you should pass time as go_ora.TimeStampTZ

[btnguyen2k]

@sijms, thanks for the response. Going with custom Go type is not a very portable solution.

I dont know how complex the implementation would be, but how about this:

• Accept Go’s built-in time.Time for all Date/Timestamp types.
• Can normalize to UTC before writing to table column.
• Write the date/time with timezone to the table column. Let the db decide to drop timezone info (if column type does not support timezone) or keep it.

Cheers,

[sijms]

ok I will test and update the code then inform you

[sijms]

fixed in v2.7.25

[sijms]

the solution you suggest give 2 issues: 1- during output parameters, data type should defined correctly so we can not use time.Time parameter which will give type DATE when using as output parameter for TIMESTAMP or TIMESTAMP with timezone field it will produce error 2- passing value time.Time which contain timezone information as input for DATE or TIMESTAMP. oracle will shift date value by timezone offset

so custom go types is needed

[btnguyen2k]

Hi @sijms, do you have a test case or code example for issue number 1?

Regarding issue number 2: dealing with date/time is not trivial. Devs should be aware of time zones at server side, client side and session time zone (if database/driver supports). Then developer and/or application has a few choices to deal with date/time/timezone. One common practice is to normalize everything to UTC before writing to database.

Using custom types is an option, too. However, this should only be used if there is no other option as this option is not portable IMHO. It is obvious that using custom types makes the code less portable. Furthermore, if the application is to support multiple database backends via a unified interface such as database/sql. Then going with common practice such as normalizing data/time to UTC, or using only standard types are much better than using custom types from a specific driver.

Maybe an interim solution for now:

• The driver supports custom types as well as standard time.Time
• If the input time.Time DOES have a timezone (any value other than UTC/GMT+0), internally it converts the input time.Time to a custom type that supports timezone.

[sijms]

ok I open the issue again I will try your suggestion and do more testing before release

[sijms]

i finish investigation and find bug related to time zone(input) and server timezone version i fix it in next release now you can use time.Time{} as input and output for DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE the test done for time zones UTC, +3, +8 using oracle servers 10, 11, 12, 19 and 21

[sijms]

the only situation that need strict type (should pass timestamp and timestamptz) is associative array. otherwise I let time.Time{} a default in/out for all oracle date types fixed in v2.8.6 testing file time_test.go