Amazon Redshift: the best way to compare dates

Question

I have an example table in redshift. I want to create a report with the wise information of the month. So far I have found three ways.

 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 best way to do this?

Answer 1

What about

"

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

"

Answer 2

I would suggest not using the second (at least) at least ... For readability and mainly because it reads naturally (at least for me), I usually use something 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) 

, datepart (mm, created_at_date) = 6

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

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 the date