Extract Date from datetime

Moderator: NorbertKrupa

Post Reply
Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Extract Date from datetime

Post by Vertica_grm » 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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Extract Date from datetime

Post by JimKnicely » 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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”