Working with HP-Vertica date and time

HP-Vertica-Date-Time.sql

/*
#1 always write datetime values into the database as UTC!!!
-- Reread that last statement
Vertica will store all datetime values as UTC values 
*/
-- I highly recommend storing an Integer column of the Day Date
select TO_CHAR(<% DATA_END_TIME %>::DATE - INTEGER '1', 'YYYYMMDD')::INTEGER AS date_id

SELECT CLOCK_TIMESTAMP() "Current Time";	-- return current time
SELECT NOW();   -- time since last session connection (or commit;) which may be an older time than now

-- get epoch from a timestamp
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2014-12-09 00:00:00')::int as epoch_from_timestamp; 
-- output
epoch_from_timestamp 
-------------------- 
1418083200           

-- get epoch from a timestamp column
SELECT EXTRACT(EPOCH FROM <column_name>)::int as epoch_from_timestamp from <schema_name>.<table_name>;

-- convert a date_id in a format like YYYYMMDD::INT to a timestamp
select TO_DATE(20141102::varchar, 'YYYYMMDD')::timestamp
select TO_DATE(DATE_GID::varchar, 'YYYYMMDD')::timestamp
-- Convert a 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, an alias for TIMESTAMP By Month
SELECT to_char(CREATION_TIMESTAMP, 'YYYYMM')::INT as YYYMM, count(column)
FROM schema.tableName
where a.CREATION_TIMESTAMP < '2014-06-30'
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

Thank you so much for sharing.