Amazon Redshift : Best way to compare dates

Question

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?

Mate

Answer 1

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

Supermarket

Answer 2

How about

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

Nonrepresentational

My question is which is the best way to compare dates ? – Mate

Answer 3

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

Austronesian

Answer 4

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

Roath

Answer 5

I prefer a fourth way:

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

Floria