CODEWITHSUNDEEP
sqlpostgresql
Tarik
Tarik

Reputation: 159

How to generate series of last 7 date from current date in postgresql?

how i will get last 7 days from current date?

suppose current date is: 2020-06-17

Output Should be:

2020-06-10,2020-06-11, 2020-06-12,2020-06-13,2020-06-14,2020-06-15,2020-06-16

Upvotes: 1

Views: 1573

Answers (1)

AdamKG
AdamKG

Reputation: 14066

testdb=# select dt::date d from generate_series(current_date - interval '7 days', current_date - interval '1 days', interval '1 days') dt;
     d      
------------
 2020-06-10
 2020-06-11
 2020-06-12
 2020-06-13
 2020-06-14
 2020-06-15
 2020-06-16
(7 rows)

Or, as an array/string:

testdb=# select array_agg(dt::date) last_7d from generate_series(current_date - interval '7 days', current_date - interval '1 days', interval '1 days') dt;
                                    last_7d                                     
--------------------------------------------------------------------------------
 {2020-06-10,2020-06-11,2020-06-12,2020-06-13,2020-06-14,2020-06-15,2020-06-16}
(1 row)

testdb=# select array_to_string(array_agg(dt::date), ',') last_7d from generate_series(current_date - interval '7 days', current_date - interval '1 days', interval '1 days') dt;
                                   last_7d                                    
------------------------------------------------------------------------------
 2020-06-10,2020-06-11,2020-06-12,2020-06-13,2020-06-14,2020-06-15,2020-06-16
(1 row)

Upvotes: 3

Related Questions