I am asking Oracle to tell me what the date & time will be in Santiago when it is 12:00 midday next Monday in localtime.
The following SQL:
SELECT to_timestamp(
to_char(trunc(next_day(sysdate, 1)) + 0.5,
'DD-MM-YY HH24:MI'))
AT TIME ZOME 'America/Santiago'
FROM dual;
Returns: 16-08-21 06:00:00,000000000 AMERICA/SANTIAGO
I want to extract the date, hours & minutes, so I am doing:
SELECT to_char(
to_timestamp(
to_char(trunc(next_day(sysdate,1)) + 0.5,
'DD-MM-YY HH24:MI'))
AT TIME ZONE 'America/Santiago',
'DD-MM-YY HH24:MI')
FROM dual;
Returns: 16-08-21 07:00
Can anyone explain why 06:00 becomes 07:00 when converting to_char?!
thanks in advance
This is not a complete answer; using the "answer" format so that I can post formatted code. I may edit this answer if I can find out more.
First, to isolate the problem, it is best to write the simplest possible example that reproduces the error. This has nothing to do with the next_day function, or with the initial conversions - it's all about the last conversion, from timestamp with time zone to string.
In the query below, I select a hard-coded timestamp with time zone literal (displayed on my screen using my session's nls_timestamp_tz_format setting), and also the same timestamp converted to string implicitly (also using the same nls_timestamp_tz_format model). As you can see, the error is reproduced - and we get more information: When Oracle converts the timestamp to a string, it changes the time zone DST flag (it shows CLST instead of CLT; CLST is Chile Summer time, which shouldn't apply in August - that's winter in the Southern hemisphere).
Why this happens is a good question. The first place to look is the time zone files - very likely there is a bug there, and there may be patches. (Patches for time zone files, and updates, etc., should be maintained regularly regardless of this problem.) I am able to replicate this on my system because I am an amateur using a free version of Oracle, which doesn't come with access to patches and updates and such; but this is where I would look first.
If that doesn't help, perhaps it's time to ask Oracle themselves (raise a service request, or perhaps report as a bug).
So - here is my nls_timezone_tz_format first, to make sure we know what's happening:
select value
from v$nls_parameters
where parameter = 'NLS_TIMESTAMP_TZ_FORMAT'
;
VALUE
----------------------------------------------------------------
yyyy-mm-dd hh24:mi:ss.ff3 tzr tzd
Then here is the simple(st) query that reproduces the problem. Note that the "timestamp...." thing is a hard-coded constant (literal), up to and including the time zone specification.
select timestamp '2021-08-16 12:00:00 America/Santiago' as tz
, to_char(timestamp '2021-08-16 12:00:00 America/Santiago') as str
from dual
;
TZ STR
-------------------------------------------- ---------------------------------------------
2021-08-16 11:00:00.000 America/Santiago CLT 2021-08-16 12:00:00.000 America/Santiago CLST