CODEWITHSUNDEEP
sqlamazon-redshift
sql-noob
sql-noob

Reputation: 61

Generate X days from a given date

Graphs usually have an X axis that contains a consecutive flow. Usually this would work if the union with another table contains the datetime, but in the case when that date does not actually have any values, this results in my graph to "skip" that days point. 

select TO_CHAR((dateAdd(day,-row_number() over(order by true), '12/8/2018 
12:00:00 AM'))::datetime, 'MM/dd/yyyy') as Display, 0 as consumption, 0 as 
masterconsumption 
limit 7

In the example above, I am trying to generate the past 7 days timestamp. So it would be something like 12/8/2018 | 12/7/2018... | 12/1/2018.

I thought providing the limit would work but the results always come back with just the first date 12/8/2018

Upvotes: 1

Views: 55

Answers (2)

AlexYes
AlexYes

Reputation: 4208

You're getting just one value because you're selecting a scalar like select '12/8/2018 12:00:00 AM'. Wrapping it into other functions and adding limit 7 doesn't increase the number of rows returned. The trick to return multiple rows is to select from some table though you're not actually specifying any columns from that table, just add from clause:

select TO_CHAR((dateAdd(day,-row_number() over(order by true), '12/8/2018 
12:00:00 AM'))::datetime, 'MM/dd/yyyy') as Display, 0 as consumption, 0 as 
masterconsumption 

from some_table_that_has_at_least_7_rows

limit 7

Upvotes: 1

sql-noob
sql-noob

Reputation: 61

Here is the way to do it for those that run into it later:

select date '12/8/2018 12:00:00 AM' - ('1 day'::interval)*i as Display, 0 
as consumption, 0 as masterconsumption
from generate_series(0, extract(doy from date '12/8/2018 12:00:00 AM')::int 
- 1) as i
limit 7

Upvotes: 1

Related Questions