Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

TIMESTAMP with T as separator (ISO 8601)

For TIMESTAMP and TIMESTAMPTZ fields, the Vertica documentation specifies the following formats:

 

2004-10-19 10:23:54

2004-10-19 10:23:54+02

 

Does Vertica officially support input in ISO 8601 format with 'T' as separator? It appears to work via SQL console, but I'd like to know if there are any issues, and if it will work correctly for all input methods.

Example with 'T' separator:

 

2004-10-19T10:23:54

2004-10-19T10:23:54+02

2004-10-19T10:23:54+05:30

2004-10-19T10:23:54.123+05:30

 

Comments

  • You can specify a format when you insert data to a table or select data from a table.

     

    Example:

     

    dbadmin=> create table t1 (c1 timestamp);
    CREATE TABLE

     

    dbadmin=> insert into t1 values (to_timestamp('2004-10-19T10:23:54', 'yyyy-mm-dd HH24:mi:ss'));
     OUTPUT
    --------
          1
    (1 row)

     

    dbadmin=> commit;
    COMMIT


    dbadmin=> select * from t1;
             c1         
    ---------------------
     2004-10-19 10:23:54
    (1 row)

     

    dbadmin=> select to_char(c1, 'yyyy-mm-ddThh24:mi:ss') from t1;
           to_char      
    ---------------------
     2004-10-19T10:23:54

    (1 row)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.