Oracle Date and Timestamps scan to wrong time.Time zone or wrong time with right zone, respectively.

[RileyRaschke]

I believe this was nearly solved in: https://github.com/sijms/go-ora/issues/24

I just re-ran my test with v2.7.11 on with go1.20.7.

Given the following struct and SQL query:

type TimeDebug struct {
    CurrentDate          string    `db:"CURRENT_DATE"`
    CurrentTimestamp     string    `db:"CURRENT_TIMESTAMP"`
    Sysdate              string    `db:"SYSDATE"`
    Systimestamp         string    `db:"SYSTIMESTAMP"`
    TimeCurrentDate      time.Time `db:"T_CURRENT_DATE"`
    TimeCurrentTimestamp time.Time `db:"T_CURRENT_TIMESTAMP"`
    TimeSysdate          time.Time `db:"T_SYSDATE"`
    TimeSystimestamp     time.Time `db:"T_SYSTIMESTAMP"`
}

var (
    query string = `
        SELECT
            to_char(CURRENT_DATE, 'YYYY-MM-DD HH24:MI:SS') "CURRENT_DATE",
            to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR') "CURRENT_TIMESTAMP",
            to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE",
            to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR') "SYSTIMESTAMP",
            CURRENT_DATE as T_CURRENT_DATE,
            CURRENT_TIMESTAMP as T_CURRENT_TIMESTAMP,
            SYSDATE as T_SYSDATE,
            SYSTIMESTAMP as T_SYSTIMESTAMP
         FROM DUAL`
)

And the following Testing Functions

func TestDbc(dbc *sql.DB) {
    r := TimeDebug{}
    row := dbc.QueryRow(query)
    err := row.Scan(
        &r.CurrentDate, &r.CurrentTimestamp, &r.Sysdate, &r.Systimestamp,
        &r.TimeCurrentDate, &r.TimeCurrentTimestamp, &r.TimeSysdate, &r.TimeSystimestamp,
    )
    if err != nil {
        fmt.Printf("scan error: %#v", err)
    }
    DumpJson(r)
    DumpTime(r)
} 

func DumpJson(d any) {
    b, _ := json.MarshalIndent(d, "", "  ")
    fmt.Printf("%s\n", string(b))
}

func DumpTime(r TimeDebug) {
    fmt.Printf("%16s: %s\n", "Sysdate", r.TimeSysdate.Format(time.UnixDate))
    fmt.Printf("%16s: %s\n", "Systimestamp", r.TimeSystimestamp.Format(time.UnixDate))
    fmt.Printf("%16s: %s\n", "Real Time", time.Now().Format(time.UnixDate))
}

Yields the following output. Note the Time prefixed items are scanned to type time.Time while the string fields are using oracle TO_CHAR to produce the real time.

{
  "CurrentDate": "2023-08-07 10:07:39",
  "CurrentTimestamp": "2023-08-07 10:07:39 -05:00",
  "Sysdate": "2023-08-07 10:07:39",
  "Systimestamp": "2023-08-07 10:07:39 -05:00",
  "TimeCurrentDate": "2023-08-07T10:07:39Z",
  "TimeCurrentTimestamp": "2023-08-07T15:07:39.24578-05:00",
  "TimeSysdate": "2023-08-07T10:07:39Z",
  "TimeSystimestamp": "2023-08-07T15:07:39.245776-05:00"
}
         Sysdate: Mon Aug  7 10:07:39 UTC 2023
    Systimestamp: Mon Aug  7 15:07:39 -05:00 2023
       Real Time: Mon Aug  7 10:07:39 CDT 2023

Above we can see

• sysdate scanned to time.Time is the correct time, but defaults to wrong zone.
• systimestamp scanned to time.Time is the right zone, but the wrong time.

I believe the issue resides in v2.7.11 here

I think the solution is to default to the zone resulting from select to_char(systimestamp,'TZR') from dual; for both date and timestamp oracle types with a connection option being added to use any specific zone as the default and override the above assumption.

The above is the approach used by github.com/godror/godror godror has a whole document on this subject and oracle's poor presumptions at: timezone.md

I'd love to use your instant client free driver, but until it can tell time, I cannot.

I have a full version of my test ready to run at: https://github.com/RileyRaschke/goora_date_test

[sijms]

I find some issues related to reading time zone as describe in issue #419 after fixing these issue i get the following result: go result

{
  "CurrentDate": "2023-08-12 11:17:26",
  "CurrentTimestamp": "2023-08-12 11:17:26 +03:00",
  "Sysdate": "2023-08-12 11:17:26",
  "Systimestamp": "2023-08-12 11:17:26 +03:00",
  "TimeCurrentDate": "2023-08-12T11:17:26Z",
  "TimeCurrentTimestamp": "2023-08-12T11:17:26+03:00",
  "TimeSysdate": "2023-08-12T11:17:26Z",
  "TimeSystimestamp": "2023-08-12T11:17:26.94+03:00"
}
         Sysdate: Sat Aug 12 11:17:26 UTC 2023
    Systimestamp: Sat Aug 12 11:17:26 +03:00 2023
       Real Time: Sat Aug 12 11:04:22 +03 2023

SQL developer result (after 2 minutes)

{
  "CurrentDate": "2023-08-12 11:19:55",
  "CurrentTimestamp": "2023-08-12 11:19:55 ASIA/RIYADH",
  "Sysdate": "2023-08-12 11:19:55",
  "Systimestamp": "2023-08-12 11:19:55 +03:00",
  "TimeCurrentDate": "12-AUG-23",
  "TimeCurrentTimestamp": "12-AUG-23 11.19.55.000000000 AM ASIA/RIYADH",
  "TimeSysdate": "12-AUG-23",
  "TimeSystimestamp": "12-AUG-23 11.19.55.524000000 AM +03:00"
}

[sijms]

fixed in v2.7.12