Redshift Date Time Operations

Redshift DateTime Operations.sql

-- ************************************************************
-- Substract 10 days from 25 Jan 2018
SELECT '2018-01-25'::date - interval '10 day' val

-- ************************************************************
-- Substract date via interval
SELECT '2018-01-15'::date - interval '1 day' interval_val
SELECT '2018-01-15'::date - interval '1 week' interval_val
SELECT '2018-01-15'::date - interval '1 month' interval_val
SELECT '2018-01-15'::date - interval '1 year' interval_val
SELECT '2018-01-15'::date - interval '1 century' interval_val

-- ************************************************************
-- Substracting days with dynamic value. Here the value 50 can be replace with a scalar value from SELECT statement
SELECT '2018-01-15'::date - 50 * interval '1 day' interval_val

-- ************************************************************
-- Generate datetime series with 364 rows, and increments time by minute
-- WARNING: generate_series supported only by the master\leader node and fails to work 
--          while join it with tables in compute nodes. The operation will rause the following error
-- "ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables."
SELECT timestamp '2018-01-25 00:00:00' + (i * interval '1 minute')
FROM  generate_series(1, (date '2018-12-31' - date '2018-01-01')) i;


-- ************************************************************
-- Generate date series with CTE recursion
WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(days,'2014-01-01','2022-01-01') 
)
SELECT n as id,trunc('2014-01-01'::date + n * interval '1 day') as date_list FROM numbers;

-- ************************************************************
-- Generate date series with CTE and populate to DateDimension table

CREATE TEMP TABLE DateDim(ID integer,date_v date)

INSERT INTO DateDim 
WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(days,'2014-01-01','2022-01-01') 
),
date_list as (
	SELECT n as id,trunc('2014-01-01'::date + n * interval '1 day') as date_v FROM numbers
  )
SELECT * FROM date_list


-- ************************************************************
-- Truncate time component from date-time value

select trunc('2018-01-25 01:01:10'::timestamp)
select trunc(timestamp '2018-01-25 01:01:10')
-- Output: 2018-01-25	

select trunc(sysdate)
-- Outputs Date Component

-- ************************************************************
-- DateDiff with different intervals
select DATEDIFF(second, sysdate, sysdate + interval '30 seconds')
select DATEDIFF(minute, sysdate, sysdate + interval '20 minutes')
select DATEDIFF(hour, sysdate, sysdate + interval '2 hours')
select DATEDIFF(day, sysdate, sysdate + interval '1 day')
select DATEDIFF(week, sysdate, sysdate + interval '1 week')
select DATEDIFF(month, sysdate, sysdate + interval '1 month')
select DATEDIFF(year, sysdate, sysdate + interval '1 year')

-- ************************************************************
-- Extract date component from value
select extract(day from date '2022-02-22')

-- Get year from epoch value
select extract(YEAR FROM date(timestamp 'epoch' + '1523625632' / 1 * interval '1 second')) as year

-- Get day of the week based on ISO 8601
SELECT date_part('dow',TIMESTAMP '2021-03-19 10:00:00') dow
-- Get day of the year based on ISO 8601
SELECT date_part('doy',TIMESTAMP '2021-03-19 10:00:00') doy;

-- Get Day name from date. Example: Monday
select to_char(current_date, 'Day')

-- Get Month name from date. Example: January 
select to_char(current_date, 'Month')

-- Get last day of the month
select last_Day(current_date)

-- Get date from week number. Get first week for the year 2021
select to_date(1||' '||2021,'WW YYYY')

--  Get week from date, in ISO 8601, where Monday is the first day of the week
select extract(week from '2022-01-02'::date);  
select date_part(w, '2022-01-02'::date);

-- Get the start date of the week, with Monday as starting week
select date_trunc('week','2022-01-14'::date)::date


--International Standard ISO 8601, where Monday is the first day of the week
-- Get first week of the month for 20220203
select to_date(1||' '||20220203,'W YYYYMMDD')

-- Get second week of the month for current_date
select to_date(3||' '||to_char(current_date,'YYYYMMDD'),'W YYYYMMDD')

-- Get date for tenth week of the year 2021, starts from 1-52
-- 'WW' the first week starts on the first day of the year
select to_date(1||' '||2022,'WW YYYY')

--Get week for the month. Week of month (1–5; the first week starts on the first day of the month.)
select to_char(to_date(20220314,'YYYYMMDD'),'W')

--Get week for the year. starts from 1-52
select to_char(to_date(20220314,'YYYYMMDD'),'WW')

--Get ISO week number of year (the first Thursday of the new year is in week 1.)
select to_char(to_date(20220101,'YYYYMMDD'),'IW')