Date and time in PostgreSQL are represented by following data types: timestamp without time zone, timestamp with time zone, date, time with time zone, time without time zone, interval

The query below lists all columns with date/time data types.

Query

select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('timestamp without time zone', 
                        'timestamp with time zone',
                        'time with time zone',
                        'time without time zone',
                        'interval', 'date')
      and col.table_schema not in ('information_schema', 'pg_catalog')
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

• table_schema - 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
• datetime_precision - the number of fractional digits retained in the seconds field

Rows

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

Sample results