Vertica Dates

Vertica_Dates

-- convert a date_id (e.g., DIM_DATE_GID) in a format like YYYYMMDD::INT to a timestamp
select TO_DATE(20141102::varchar, 'YYYYMMDD')::timestamp
select TO_DATE(DIM_DATE_GID::varchar, 'YYYYMMDD')::timestamp
-- convert date type to an int
select to_char(current_date(), 'YYYYMMDD')::int
-- Using Date math to find the number of days to use in DATEs as INT values.
-- This is helpful since months can have a varying number of days (e.g., Feb 28th or 29th, Sept. 30th or Dec 31st)
select 
 current_date() as Current_Date_as_Date
, to_char(current_date(),'YYYYMMDD')::integer as Current_Date_as_Integer
, to_char(current_date() - integer '1','YYYYMMDD')::integer as Today_Minus_One_day 
, to_char(current_date() - integer '7','YYYYMMDD')::integer as Today_Minus_Seven_days
, DATEDIFF('DAY', 19960220::varchar::date, 19960320::varchar::date) as Days_Diff_within_LeapYear
-- Group DATETIME By Month
SELECT to_char(CREATION_TIMESTAMP, 'YYYYMM')::INT as YYYMM, count(a.column)
FROM schema.table a
group by 1 order by 1 desc;
-- Group By Month where DATE is an INT
SELECT (DATE_ID / 100)::INT as YYYYMM, COUNT(DISTINCT USER_ID) as uu_cnt