Timezone settings



Last updated on December 21st, 2015 at 06:15 am

I am presenting here the some of the good information on timezone settings

How to find the latest DST patch applied
SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

This select gives all TimeStamp with Time Zone (TSTZ) columns in your database:
select c.owner || ‘.’ || c.table_name || ‘(‘ || c.column_name || ‘) -‘
|| c.data_type || ‘ ‘ col
from dba_tab_cols c, dba_objects o
where c.data_type like ‘%WITH TIME ZONE’
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = ‘TABLE’
order by col

This select gives all TimeStamp with Local Time Zone (TSLTZ) columns in your database:
select c.owner || ‘.’ || c.table_name || ‘(‘ || c.column_name || ‘) -‘
|| c.data_type || ‘ ‘ col
from dba_tab_cols c, dba_objects o
where c.data_type like ‘%WITH LOCAL TIME ZONE’
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = ‘TABLE’
order by col
/

This select give the database timezone
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
————–
UTC

How can I check the session time zone?

SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
—————
+01:00


Leave a Reply