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?
Extract Date from datetime
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Extract Date from datetime
Hi!
The DATE data type in Vertica only stores a date... no time.
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:
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:
When you can now do this:
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)
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)
That is, don't do this:
Code: Select all
dbadmin=> select count(*) from t where trunc(c) = '2015-02-16';
count
-------
1
(1 row)
Code: Select all
dbadmin=> select count(*) from t where d = '2015-02-16';
count
-------
1
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.