Find all date and time columns in Vertica database

Date and time in Teradata are represented by common sql date types: Date, Time, Time with timezone, Timestamp, Timestamp with timezone and numerous of Interval data types.

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


select table_schema,
       ordinal_position as col_no,
from v_catalog.columns c
where data_type like 'interval%'
      or data_type like 'date%'
      or data_type like 'time%'
order by table_schema,


  • table_schema - name of the database
  • table_name - name of the table
  • col_no - column position in table
  • column_name - name of the column
  • data_type - type of data:
    • date
    • time
    • timeTz
    • timestamp
    • timestampTz
    • interval year
    • interval year to month
    • interval month
    • interval day
    • interval day to hour
    • interval day to minute
    • interval
    • interval hour
    • interval hour to minute
    • interval hour to second
    • interval minute
    • interval minute to second
    • interval second


  • 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 and table name

Sample results

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