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

  • marcothesanemarcothesane - Select Field - Administrator

    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_KnicelyJim_Knicely - Select Field - 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.