Amazon Redshift - date manipulation functions

Amazon redshift- date manipulation functions?

--to select the current date and timestamp in redshift use:

select current_date
select current_timestamp

--to add months and convert date to integer use in redshift:

 select to_char(add_months(to_date(current_date, 'YYYY-MM-DD'), -1), 'YYYYMMDD')

Note: Do not use to_date(current_date, 'YYYYMMDD') since redshift has a bug with this function and handles some dates wrong. Atleast this is what I found from my experience.

--to add months and get first of last month:

select to_char(add_months(to_date(current_date, 'YYYY-MM'), -1), 'YYYYMM') ||'01'

--to add months and convert date to integer use:

select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id

select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'999999') --to get month id
select date(current_date -  cast('1 month' as interval))

--to extract year part from the current date

 select extract (year from current_date)
 select date_trunc('year', current_date)
 select to_number(to_char(current_date,'YYYY'),'9999')  --integer format

--to extract day and month from the current date

 select extract (day from current_date)

--to get first and last day of the year:

select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')

--to get first of the month given a date in redshift use:

 select date_trunc('month', current_timestamp)

--to get last month end date

 select date_trunc('month', current_timestamp)-1

--to add 7 days to current date

select date(current_date +  cast('7 days' as interval))
select date(current_date +  cast('7 days' as interval))

--to get timestamp in a specific format

 select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
 select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')

--to add hours to a date

  select TO_CHAR(date((current_date +  cast('1 hour' as interval))),'YYYY-MM-DD')

--to converts the date 02 Dec 2011 into the default date format:

select to_date ('02 Dec 2011', 'DD Mon YYYY');