ORACLE 12.2.0.1 ON ORACLE LINUX 7.9 (not relevant: I get the same with Oracle 19.3.0.0.0 on Windows 11)
Yesterday a poster asked about inserting a timestamp with time zone, where he only provided a time zone abbreviation like CDT or EDT but no time zone name like ‘America/Chicago’ or ‘Jamaica’.
In researching that question I came upon something that to me seems weird.
If we call TO_DATE or TO_TIMESTAMP or TO_TIMESTAMP_TZ with an incomplete format model and corresponding input string, Oracle will provide default values for the missing components.
For month and year, Oracle will use the current month and year. For day, hour, minute, second it will use the minimum values possible (1, resp. 00, 00, 00).
For example:
select to_date('05 30', 'dd mi') as dt from dual;
DT
-------------------
2023-05-05 00:30:00
I only gave the “day-of-the-month” and the “minutes” components; Oracle used them, and used current month and year, and 00 for “hours” and “seconds”.
The interesting question is, “_current_” according to what? Suppose my DB time zone is UTC, my client OS time zone (on the laptop I run my queries on) is America/Los_Angeles, and the session time zone is Australia/Sydney - for example because I live in L.A., I don't change the time zone on my OS when I travel, but I am currently in Sydney and for my queries I set the session time zone explicitly with ALTER SESSION.
So, what are the “current” month, and possibly year? Current in London, in Los Angeles, or in Sydney? Around the end of each month, the “current month” (and “current year”) may be different; so which one is used?
The documentation is silent about this particular question. All it says is
If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.
for example here: https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/datetime-data-types-and-time-zone-support.html#GUID-4D95F6B2-8F28-458A-820D-6C05F848CA23
No clarification as to what “current” means. "Month" very likely means “month and year” - the “current month” right now is May 2023, not just May. (Also, the documentation doesn't actually say that each individual component of the date or the time can be missing - it only says what happens if the time-of-day, or the date part, is missing in its entirety.)
However, a bit of experimentation shows that “current” (month and year) means “as reported by SYSDATE and SYSTIMESTAMP” - the “current month” of the OS on the client computer.
OK, so now I come to the oddity or bug in my title. TO_TIMESTAMP_TZ requires not just date and time components, but also time zone information. If we don't include it, does it use a default? And if it does, what is it?
Answer: it will indeed use a default time zone region (or offset)… but it is the SESSION time zone! Why on Earth would Oracle do this? Why not the OS time zone, like it does for other defaults? I can't tell if this is intended, since I didn't find it in the documentation (and I strongly doubt that it is documented), or if it is a bug; in any case, it's inconsistent.
To make the example below work, I had to mess with my computer - I made it think the “current date” is May 31, so that if my OS is in Los Angeles - as it actually is - but I set the session time zone to Australia/Sydney, the “session” date is June 1. So if a default is used for month, when I use an incomplete model, I should get… May or June? The surprising answer is that Oracle will still use May, but attach the Australian time zone. This makes no sense to me. Compare to CURRENT_TIMESTAMP, which reports the current moment in time at the session time zone; here the date part is, correctly, June 1, attached to the Australian time zone.
Here is the example (again, remember that I told my system that today is May 31).
alter session set time_zone = 'Australia/Sydney';
select systimestamp, current_timestamp, to_timestamp_tz('22:30', 'hh24:mi') from dual;
SYSTIMESTAMP CURRENT_TIMESTAMP TO_TIMESTAMP_TZ('22:30','HH24:MI')
------------------------------------- --------------------------------------------- ---------------------------------------------
2023-05-31 11:04:29.024 -07:00 -07:00 2023-06-01 04:04:29.024 Australia/Sydney AEST 2023-05-01 22:30:00.000 Australia/Sydney AEST