Menu
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.
trunc(created_at_date) between '2014-06-01' and '2014-06-30'
created_at_date like '2014-06%'
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?
431
SELECT DATEDIFF ('datepart', <date | time | timetz | timestamp>, <date | time | timetz | timestamp>); Here above syntax is used to find the difference between the specified dates wised columns in a table.
select extract(minute from sysdate); -- hour, day, month, year, century select date_part(minute, sysdate); -- hour, day, month, year, century -- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday SELECT extract(dow from sysdate); SELECT extract(dow, sysdate); -- returns a string like monday, tuesday, etc select ...
You can get real-time insights in seconds without managing complex pipelines. Amazon Redshift with Kinesis Data Streams is fully managed, and you can run your streaming applications without requiring infrastructure management.
A COPY command is the most efficient way to load a table. You can also add data to your tables using INSERT commands, though it is much less efficient than using COPY. The COPY command is able to read from multiple data files or multiple data streams simultaneously.
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
160
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
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
