Generate X days from a given date

Question

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

The `::datetime` looks like Postgres which supports`generate_series` — dnoeth, Jan 08 '19 at 16:18
https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql — Tony, Jan 08 '19 at 16:34
This would not work for Amazon RedShift by the way. I have tried and it seems that it's not supported — sql-noob, Jan 08 '19 at 16:54
See https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift — dnoeth, Jan 08 '19 at 16:58

score 1 · Answer 1 · answered Jan 08 '19 at 17:07

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

score 1 · Accepted Answer · answered Jan 09 '19 at 09:39

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

Generate X days from a given date

2 Answers2