I’m trying to get the local time on my server.
select sysdatetime()
, getdate()
, sysdate
, now()
, sysdateutc
, getutcdate()
, utc_date
, nowutc()
The above statements seem to give UTC time with or without UTC option:
My system is 12:10; located UTC+1, winter time.
I would like to get the real local time of my server.
The function sysdate should return the local time, whereas sysdateutc returns the UTC-time. All other SQL-functions are just variants of the first two.
An analysis will be made for this possible bug.
A bug has been identified which on the 23.0-release sysdate returns UTC time instead of local time when evaluated in a PSQL-scope.
However, that bug didn’t cause your question.
In this case, the question is triggered due to the way dates are displayed in the Query Tool. All dates are displayed in UTC-time.
For example, when executing:
select sysdateutc
, to_char(sysdateutc, 'YYYYMMDDHH24MISS') sysdateutctxt
, sysdate
, to_char(sysdate, 'YYYYMMDDHH24MISS') sysdatetxt
the results will be:
Which displays that the actual date value is in local timezone (as visible in column sysdatetxt), but the displayed value is in UTC.
A similar question can arise when calculating the time difference between local time and UTC-time as in:
select sysdateutc - sysdate
The outcome is 0, since both refer to the same time. This can feel counterintuitive at first. Although the wall clock large hand points to another hour in UTC and CET, the actual time is identical.
In general it is recommended to only work with UTC times when applications may bridge timezones or timezone settings.
In case it is necessary to process the difference in seconds between local and UTC time, please use utcoffset as in:
select utcoffset
For CET, the outcome will be 3600 currently. In DST that would be 7200.
Please note that there are locations on the world with fractional hour offsets compared to UTC.
A somewhat hacky approach could be to add the needed offset to the UTC time as in:
select sysdateutc + utcoffset / 86400
However, note that the resulting date/time is internally still a UTC time, which happens to match some other local time. One of the most frequently found issues in setting up time in the past was that someone changed fiddled with the timezone and clock to get a value matching the wallclock. However, often the system clock was off by for instance one hour and the timezone in the other direction. So when using this hack, please make sure to clearly reflect the meaning of the date/time in variable and colum naming.
I think the issue is present somewhere because one of my script doesn’t fill the right time.
Try this:
create or replace table test@inmemorystorage
as
select sysdate as my_datetime;
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') as sysdatetxt
, my_datetime as test_original
, to_char(my_datetime, 'YYYY/MM/DD HH24:MI:SS') as test_sysdatetxt
from test@inmemorystorage;
with result:
More test here:
create or replace table test@inmemorystorage as
select 'create/replace' as task
, sysdate as my_datetime_original
, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') as my_datetime_text
;
insert into test@inmemorystorage
( task
, my_datetime_original
, my_datetime_text
)
values
( 'insert'
, sysdate
, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
);
insert into test@inmemorystorage
( task
, my_datetime_original
, my_datetime_text
)
values
( 'insert then update'
, sysdate
, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
);
update test@inmemorystorage
SET my_datetime_original = sysdate
, my_datetime_text = to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
where task = 'insert then update'
;
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') as sysdatetxt
, task, my_datetime_original as test_original
, to_char(my_datetime_original, 'YYYY/MM/DD HH24:MI:SS') as to_char_before_print
, my_datetime_text as to_char_before_insert
from test@inmemorystorage
;
Can you please illustrate what the difference is in these test scripts between actuals shown and expectation?
you see that the insert insert a utc value instead of sysdate.
By using a to_char() when inserting, correct time is inserted.
So I think this is not only an issue of QueryTool “translating” the time into utc time in the result window, this occurs also when inserting datetime value into a database field
same behaviour with @sqlserver container
insert into test@my_sqlserver (my_datetime) values (sysdate)
will insert utc time.
