Useful Redshift Functions for date and time analysis

Halesha_Bandri — Wed, 02 Jul 2025 14:08:05 GMT

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)

DATEADD(second,59,
DATEADD(minute,59,
DATEADD(hour,23,
DATEADD('day',-1,TRUNC(ADD_MONTHS(TRUNC(DATE_TRUNC('quarter',sent_email_datetime)), 3)))
))) AS quarter_end_qlik, // o/p:= timestamp (quarter_end_date+23:59:59)

DATEADD(second,59,
DATEADD(minute,59,
DATEADD(hour,23,
DATEADD('day',-1,TRUNC(ADD_MONTHS(TRUNC(DATE_TRUNC('year',sent_email_datetime)), 12)))
))) AS year_end_qlik // o/p:= timestamp (year_end_date+23:59:59)

FROM $(vConnectionPath).gcdw_rpt.content_effectiveness_sent_email_vw

LIMIT 10000
;

topic Useful Redshift Functions for date and time analysis in App Development

Useful Redshift Functions for date and time analysis