The Vertica Database Forums

Posted: **Sun Jun 28, 2015 4:17 am**

I have a column with datatype as timestamp. There are thousands of entries for that column. I need a command which would just keep the data part in yyyy-mm-dd

E.g. input :2015-02-16 16:47:22
Output 2015-02-16

Any suggestions?

Posted: **Sun Jun 28, 2015 1:14 pm**

Hi!

The DATE data type in Vertica only stores a date... no time.

Code: Select all

dbadmin=> select '2015-02-16 16:47:22'::date;
  ?column?
------------
 2015-02-16
(1 row)

If you have a table with a column that is a TIMESTAMP, one option is to add another column that has a data type of DATE with a default value that converts the original TIMESTAMP column to a DATE.

Example:

Code: Select all

dbadmin=> \d t;
                                  List of Fields by Tables
 Schema | Table | Column |   Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------+------+---------+----------+-------------+-------------
 public | t     | c      | timestamp |    8 |         | f        | f           |
(1 row)

dbadmin=> select * from t;
          c
---------------------
 2015-02-16 16:47:22
(1 row)

dbadmin=> alter table t add column d date default c::date;
ALTER TABLE
dbadmin=> select * from t;
          c          |     d
---------------------+------------
 2015-02-16 16:47:22 | 2015-02-16
(1 row)

When you can (i.e. where you do not care about time), try to operate on DATE columns instead of TIMESTAMP columns, especially on large data sets.

That is, don't do this:

Code: Select all

dbadmin=> select count(*) from t where trunc(c) = '2015-02-16';
 count
-------
     1
(1 row)

When you can now do this:

Code: Select all

dbadmin=> select count(*) from t where d = '2015-02-16';
 count
-------
     1
(1 row)

The Vertica Database Forums

Extract Date from datetime

Extract Date from datetime

Re: Extract Date from datetime