CODEWITHSUNDEEP
sqldateamazon-redshift
untitled
untitled

Reputation: 1335

Amazon Redshift : Best way to compare dates

I have a sample table in redshift. I want to generate a report with the month wise data. I found the below three ways so far.

  1. trunc(created_at_date) between '2014-06-01' and '2014-06-30'

  2. created_at_date like '2014-06%'

  3. created_at_date >= '2014-06-01 00:00:00' and created_at_date <= '2014-06-30 23:59:59'

    What is the best and optimal way to do so?

Upvotes: 9

Views: 18884

Answers (5)

tconbeer
tconbeer

Reputation: 5815

I prefer a fourth way:

  1. date_trunc('month', created_at_date) = '2014-06-01'

Upvotes: 1

Rakesh Singh
Rakesh Singh

Reputation: 170

How about 

created_at_date between to_date('20140601','YYYYMMDD') and to_date('20140630','YYYYMMDD')

Upvotes: 2

SwapSays
SwapSays

Reputation: 465

Not the 1st one as it perform unnecessary (unless you really have such unprocessed data) truncate.

 1. trunc(created_at_date) between '2014-06-01' and '2014-06-30';

Definitely not this one for obvious reasons (like)

 2. created_at_date like '2014-06%'

May be this one:

 3. created_at_date >= '2014-06-01 00:00:00' and created_at_date <= '2014-06-30 23:59:59'

However, since the requirement is to generate monthly reports which I would assume to be a recurring task and on multiple data sources, I would suggest creating a one time calendar table.

This table would have mapping of a date to a month value and then you can simply join your source data with that table and group by the "month" column.

P.S. Just realized I replied to a very 'ancient' question :p

Upvotes: 6

CodingMatters
CodingMatters

Reputation: 1451

http://docs.aws.amazon.com/redshift/latest/dg/r_DATE_CMP.html

select caldate, '2008-01-04', date_cmp(caldate,'2008-01-04') from date

Upvotes: 2

fahad daniyal
fahad daniyal

Reputation: 269

I would suggest not to use the second one (with like) at least... For readability and mostly because it reads naturally (at least to me), i usually use some thing like 

select col1, col2 , 
   datepart(d, created_at_date) , datepart(mm, created_at_date) 
 from ... 
   group by datepart(d, created_at_date) , datepart(mm, created_at_date)

If you want to filter by a particular month or year stick in a where datepart(mm, created_at_date) = 6

have a look at http://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

Upvotes: 0

Related Questions