Extract Time from a Date

[Deleted User]

Jim Knicely authored this tip.

There are multiple ways to extract just the time from date in Vertica. Which method you choose depends on your preferred result data type.

Example:

dbadmin=> CREATE TABLE public.test AS
dbadmin-> SELECT sysdate,
dbadmin->        CAST(sysdate AS TIME) just_time_as_time,
dbadmin->        TO_CHAR(sysdate, 'HH:MI:SS:US') just_time_as_varchar
dbadmin-> FROM dual;
CREATE TABLE

dbadmin=> SELECT * FROM public.test;
          sysdate           | just_time_as_time | just_time_as_varchar
----------------------------+-------------------+----------------------
 2018-04-16 10:17:16.906347 | 10:17:16.906347   | 10:17:16:906347
(1 row)

dbadmin=> \d public.test
                                           List of Fields by Tables
 Schema | Table |        Column        |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+----------------------+--------------+------+---------+----------+-------------+-------------
 public | test  | "sysdate"            | timestamp    |    8 |         | f        | f           |
 public | test  | just_time_as_time    | time         |    8 |         | f        | f           |
 public | test  | just_time_as_varchar | varchar(111) |  111 |         | f        | f           |
(3 rows)

Have Fun!

marcothesane

Hi All ,
You can , however, also stick to the ANSI standard:

[dbadmin@ip-10-11-12-169 parallel]$ vsql -c \
"SELECT CURRENT_TIMESTAMP AS current_ts
 , CURRENT_TIME AS current_tm
 , CURRENT_DATE AS current_dt"
          current_ts           |     current_tm     | current_dt 
-------------------------------+--------------------+------------
 2018-04-17 14:06:59.065042+00 | 14:06:59.065042+00 | 2018-04-17
(1 row)
[dbadmin@ip-10-11-12-169 parallel]$ 

Jim_Knicely

Yup! Those work great for the "current" date/time. But not when reading data from a table

dbadmin=> CREATE TABLE public.test (some_timestamp TIMESTAMP);
CREATE TABLE

dbadmin=> INSERT INTO public.test SELECT CURRENT_TIMESTAMP;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT CAST(some_timestamp AS TIME) just_time_as_time FROM public.test;
 just_time_as_time
-------------------
 10:52:52.780807
(1 row)