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.

Query

select table_schema,
       table_name,
       ordinal_position as col_no,
       column_name,
       data_type,
       datetime_precision
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_name,
         ordinal_position;

Columns

  • 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

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

Sample results

0
There are no comments. Click here to write the first comment.