Skip to main content

Extract Time from a Date

  • April 16, 2018
  • 2 replies
  • 6 views
Forum|alt.badge.img+2

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!

    2 replies

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • April 17, 2018

    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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • April 17, 2018

    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)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement