The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Extract Time from a Date

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!

Comments

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

    [[email protected] 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)
    [[email protected] parallel]$ 
    
  • Jim_KnicelyJim_Knicely Administrator

    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)
    
Sign In or Register to comment.

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