Skip to content

Instantly share code, notes, and snippets.

@jackghm
Created August 2, 2015 22:54
Show Gist options
  • Save jackghm/1b35be6930cbf3cec66e to your computer and use it in GitHub Desktop.
Save jackghm/1b35be6930cbf3cec66e to your computer and use it in GitHub Desktop.
Working with HP-Vertica date and time
/*
#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
@ruoguluo
Copy link

Thank you so much for sharing.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment