Find all date and time columns in PostgreSQL database

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.