You Asked

I want to convert a date in some time zone to date in the db timezone.
Now for some reason i get UNKNOWN as the timezone_region when i run the query -

select extract(timezone_region from systimestamp) from dual;

But I was able to get the timezone minute and timezone hour using these 2 queries

SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) into tz_hour FROM dual;
SELECT EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) into tz_min FROM dual;

I saw that I could use these values as well with the 'at time zone' to convert my date into the required timezone date.
So I ran the query -

select cast(load_timestamp as timestamp) at time zone '-7:0' from abc where <column_name> = <value>;

This runs perfectly.

But when i try to run the same thing in a pl/sql i get an exception.

set serveroutput on;
   declare
   tz_hour varchar2(10);
   tz_min varchar2(10);
   l_output date;
   tz_full varchar2(20);
   begin
   SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) into tz_hour FROM dual;
   SELECT EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) into tz_min FROM dual;
   tz_full := tz_hour||':'|| tz_min;
   dbms_output.put_line(tz_full);
   SELECT CAST(systimestamp at time zone tz_full AS DATE)
   INTO l_output from dual;
   dbms_output.put_line(l_output);
   end;
   /

Error report -
ORA-00905: missing keyword
ORA-06512: at line 11
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

Any idea guys why this is happening

and Chris said...

You're over complicating this. The function DBTimezone stores the current offset. So you can pass this to the at time zone expression.

declare
  l_output   date;
begin

  select cast( systimestamp at time zone dbtimezone as date )
  into   l_output
  from   dual;
  
  dbms_output.put_line( l_output );
  
end;
/

18-JUL-2017 13:30:42

No need to extract the values from systimestamp first!

You can't extract the timezone_region because the DB was created using hour and minute offsets, not a region.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Need further help

Arjun Talwar, July 19, 2017 - 4:32 am UTC

Thanks for the quick reply Chris.

But I have a follow up question.

I get </>+00:00</> when I run </>select dbtimezone from dual;</>. However </>select systimestamp from dual;/> returns this </>18-JUL-17 09.26.22.565133000 PM -07:00</>.

Why isn't the dbtimezone showing me the correct value i.e -7:00 ?

How should I go about it now ? (I'm confused at to which one is right)
Because of this, when I try to convert a sysdate in America/Los_Angeles timezone to my dbtimezone I'm facing issues.
Input - '06-JUN-17 01:22:42 AM' --America/Los_Angeles
Output with at time zone '-7:00' - 05-JUN-17 12.52.42.000000 PM
Output with at time zone dbtimezone - 05-JUN-17 07.52.42.000000 PM

July 19, 2017 - 10:39 am UTC

Systimestamp returns the time on your database server. So your DB server and the DB are in different time zones!

Current_timestamp gets the time in the session's time zone.

SQL> ho date
Wed Jul 19 03:37:03 PDT 2017

SQL> select current_timestamp, systimestamp, sessiontimezone, dbtimezone from dual;

CURRENT_TIMESTAMP                   SYSTIMESTAMP                        SESSIONTIM DBTIMEZONE
----------------------------------- ----------------------------------- ---------- ----------
19-JUL-17 03.37.06.838605 AM -07:00 19-JUL-17 03.37.06.838598 AM -07:00 -07:00     +00:00

SQL> alter session set time_zone = '+3:00';

Session altered.

SQL> select current_timestamp, systimestamp, sessiontimezone, dbtimezone from dual;

CURRENT_TIMESTAMP                   SYSTIMESTAMP                        SESSIONTIM DBTIMEZONE
----------------------------------- ----------------------------------- ---------- ----------
19-JUL-17 01.37.13.192019 PM +03:00 19-JUL-17 03.37.13.192011 AM -07:00 +03:00     +00:00

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Need further help

More to Explore

DBMS_OUTPUT

Error in converting a date from one timezone to other

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Need further help

More to Explore

DBMS_OUTPUT