Date are always UTC

[simulot]

I have found that datetime are returned in UTC regardless local time, session settings, database settings.

Assuming my sever is UTC, my session EST, and the local time EST

At 2020-11-16 19:58:10 local time , using go-ora to select CURRENT_DATE from dual returns 2020-11-16 19:58:10 +0000 UTC

The current code sets the the location to UTC in all circumstances:

go-ora/converters/type_conversion.go

Lines 45 to 46 in ae061e9

	return time.Date(year, time.Month(data[2]), int(data[3]),
	int(data[4]-1)+tzHour, int(data[5]-1)+tzMin, int(data[6]-1), nanoSec, time.UTC), nil

I propose to investigate on this, and propose a PR.
++

[simulot]

Um...

I have done the same test with entreprise tool and it's not good too!
entreprise's requester: (C# oledb, no idea how it's coded)
S:CURRENT_DATE, D:2020-11-16 23:26:00 +01:00
S:SYSTIMESTAMP, D:2020-11-16 22:26:00.92722 +01:00
S:CURRENT_TIMESTAMP, D:2020-11-16 23:26:00.93144 +01:00
S:LOCALTIMESTAMP, D:2020-11-16 23:26:00.93444 +01:00

+01:00 is my current time zone

go-ora tests:
SYSDATE returns 2020-11-16 22:34:12 +0000 UTC
CURRENT_DATE returns 2020-11-16 23:34:12 +0000 UTC
SYSTIMESTAMP returns 2020-11-16 22:34:12.982715 +0000 UTC
CURRENT_TIMESTAMP returns 2020-11-16 23:34:12.986287 +0000 UTC
LOCALTIMESTAMP returns 2020-11-16 23:34:12.990168 +0000 UTC

So, maybe this is not very important... what do you think ?

[sijms]

I make changes that add timezone information to the datetime in decodedate function

test and feedback

[sijms]

current_date return time only and default time zone is UTC
but systimestamp return time zone information with time

[simulot]

I have tested following query with SqlDeveloper:

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"  FROM DUAL

CURRENT_DATE        CURRENT_TIMESTAMP                                    SYSDATE             SYSTIMESTAMP                                        
------------------- ---------------------------------------------------- ------------------- ----------------------------------------------------
2020-11-25 16:08:44 2020-11-25 16:08:44 EUROPE/PARIS                     2020-11-25 15:08:44 2020-11-25 15:08:44 +00:00                          

And now with go-ora c9e66ca

CURRENT_DATE             : 2020-11-25 16:07:24
CURRENT_TIMESTAMP        : 2020-11-25 16:07:24
SYSDATE                  : 2020-11-25 15:07:24
SYSTIMESTAMP             : 2020-11-25 15:07:24

So, datetime values are now correct.

However, is it possible to add timezone information where it is expected:
CURRENT_TIMESTAMP should have time.Local (so same as client local time)
SYSTIMESTAMP should have server TZ which is usually time.UTC

And I think there are less popular timedate types which are carrying TZ.

[sijms]

this query
SELECT systimestamp, current_timestamp from dual
will give this results

System Time stamp:  2020-11-25 18:32:10.965594 +0300 +0300      
Current Times tamp:  2020-11-25 18:32:10.9656 +0300 +0300

same like sql developer

[simulot]

My fault! TO_CHAR gives a VARCHAR...

Indeed I get this:

SYSTIMESTAMP             : 2020-11-25 15:59:28.215935 +0000 UTC
CURRENT_TIMESTAMP        : 2020-11-25 16:59:28.215938 +0100 CET
CURRENT_DATE             : 2020-11-25 16:59:28 +0000 UTC
SYSDATE                  : 2020-11-25 15:59:28 +0000 UTC

... which looks good.

I have checked the application's database I'm taking care of. Most of dates are DATE... and the application mis handle time zones... So, I guess this now good enough.
Thanks.