Date and time in Oracle database are represented by following data types: date, timestamp, timestamp with time zone, timestamp with local time zone, interval.

The query below lists:

(A) all columns with date/time data types accessible to the current user in Oracle database

(B) all columns with date/time data types in Oracle database

Query

A. All columns with date/time data types accessible to the current user

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_scale
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where (data_type in ('DATE')
      or col.data_type like 'TIMESTAMP%'
      or col.data_type like 'INTERVAL%')
      and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200', 'LBACSYS')
order by col.owner,
         col.table_name,
         column_id;

B. All columns with date/time data types

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_scale as second_scale
from sys.dba_tab_cols col
join sys.dba_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where (data_type in ('DATE')
      or col.data_type like 'TIMESTAMP%' 
      or col.data_type like 'INTERVAL%')
      and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200','LBACSYS')
order by col.owner,
         col.table_name,
         column_id;

Columns

• schema_name - name of the schema
• table_name - name of the table
• column_id - column position in table
• column_name - name of the column
• data_type - type of data
• second_scale - fractional seconds precision

Rows

• One row represents one column with a date/time data type
• Scope of rows: all columns containing date/time data types in the database
• Ordered by schema name, table name and column position in table

Sample results