Useful Redshift Functions for date and time analysis
SET vConnectionPath ='your database';
LIB Connect to $(vConnectionPath); Adobe:
LOAD * ;
SQL
SELECT
sent_email_datetime, // Which is a timestamp field
// ==================================================================================================== // Set 1: MAKEDATE() / MAKETIME() // ====================================================================================================
TO_DATE('01/11/2023','DD/MM/YYYY',TRUE) AS make_date_qlik, TO_TIMESTAMP('01/11/2023 13:11:54','DD/MM/YYYY HH24:MI:SS',TRUE) AS make_timestamp_qlik,
// ==================================================================================================== // Set 2: Extracting DATE and TIME FROM TIMESTAMP // DatePart (Qlik) : Date(FLoor(TIMESTAMP_FIELD) // TimePart (Qlik) : Time(Frac(TIMESTAMP_FIELD) // ====================================================================================================
TRUNC(sent_email_datetime) AS date_part_qlik,
CAST(TRUNC(sent_email_datetime) AS TIMESTAMP) AS date_part_with_timestamp, //date followed by 00:00:00
EXTRACT(hour FROM sent_email_datetime) || ':' || EXTRACT(minute FROM sent_email_datetime) || ':' || EXTRACT(second FROM sent_email_datetime) AS time_part_qlik,
// ==================================================================================================== // Set 3: Extracting DATE PARTS and TIME PARTS FROM TIMESTAMP // i.e Extracting YEAR/MONTH/WEEK/QUARTER or HOUR/MINUTE/SECOND FROM TIMESTAMP_FIELD // ====================================================================================================
// IN REDSHIFT we can use DATE_PART() OR EXTRACT() Functions for this
// USING EXTRACT() FUNCTION EXTRACT(year FROM sent_email_datetime) AS year1, EXTRACT(quarter FROM sent_email_datetime) AS quarter1, EXTRACT(month FROM sent_email_datetime) AS month1, EXTRACT(week FROM sent_email_datetime) AS week1, EXTRACT(hour FROM sent_email_datetime) AS hour1, EXTRACT(minute FROM sent_email_datetime) AS minute1, EXTRACT(second FROM sent_email_datetime) AS second1, // USING DATE_PART() FUNCTION DATE_PART('year', sent_email_datetime) AS year, DATE_PART('quarter',sent_email_datetime) AS quarter, //1,2,3,4 DATE_PART('month', sent_email_datetime) AS month, // 1,2,3...12 DATE_PART('week', sent_email_datetime) AS week, //1,2,3,4,...52/53 DATE_PART('hour', sent_email_datetime) AS hour, DATE_PART('minute', sent_email_datetime) AS minute, DATE_PART('second', sent_email_datetime) AS second, // Additional fields DATE_PART('year',sent_email_datetime)*10 + DATE_PART('quarter',sent_email_datetime) AS year_quarter, DATE_PART('year',sent_email_datetime)*100 + DATE_PART('month',sent_email_datetime) AS year_month, DATE_PART('year',sent_email_datetime)*100 + DATE_PART('week',sent_email_datetime) AS year_week,
// ==================================================================================================== // Set 4: Creating functions like YearStart(),QuarterStart(),MonthStart(),WeekStart() // ====================================================================================================
TRUNC(DATE_TRUNC('year',sent_email_datetime)) AS year_start_qlik, TRUNC(DATE_TRUNC('quarter',sent_email_datetime)) AS quarter_start_qlik, TRUNC(DATE_TRUNC('month',sent_email_datetime)) AS month_start_qlik, TRUNC(DATE_TRUNC('week',sent_email_datetime)) AS week_start_qlik,
// ==================================================================================================== // Set 5: Creating functions like YearEnd(),QuarterEnd(),MonthEnd(),WeekEnd() // ==================================================================================================== LAST_DAY(sent_email_datetime) AS month_end, // o/p:= timestamp (month_end_date+00:00:00) DATEADD('day',-1,TRUNC(ADD_MONTHS(TRUNC(DATE_TRUNC('quarter',sent_email_datetime)), 3))) AS quarter_end, // o/p:= timestamp (quarter_end_date+00:00:00) DATEADD('day',-1,TRUNC(ADD_MONTHS(TRUNC(DATE_TRUNC('year',sent_email_datetime)), 12))) AS year_end, // o/p:= timestamp (year_end_date+00:00:00)
// ==================================================================================================== // Set 6: Creating functions like YearEnd(),QuarterEnd(),MonthEnd(),WeekEnd() ==> AS like QLik Interpretation // It is Similar to Set 5 but only Timestamp with 23:59:59 is added ( which is similar to qlik) // ==================================================================================================== DATEADD(second,59, DATEADD(minute,59, DATEADD(hour,23, LAST_DAY(sent_email_datetime) ))) AS month_end_qlik, // o/p:= timestamp (month_end_date+23:59:59)
