Vertica Quick Tip: Extract Time from a Date

This blog post was authored by Jim Knicely. 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!