CODEWITHSUNDEEP
pythonpostgresql
hydra5252
hydra5252

Reputation: 13

How can I get data from a table within in a 7 day range only?

I have a table with a column 'time' which consists of time with timestamp. It has data from January to August. I want to select that column such that only 7 days are taken at a time and this process is repeated till the end of the table.

So let's say I have data from 1th to 15th of a certain month.I first want data from 1st to 7th. Then I want data from 2nd to 8th.... and so.

Any ideas on how to do this?

P.S. I am using Python and PostGresSQL

Upvotes: 0

Views: 52

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

For a rolling window like that, I recommend first generating a series. You can do this by adjusting the first two parameters of the generate_series() call:

select begin_date, begin_date + interval '7 days' as end_date 
  from generate_series('2020-07-01'::timestamp, 
                       '2020-07-15'::timestamp - interval '7 days', 
                       interval '1 day') as gs(begin_date)
;

     begin_date      |      end_date       
---------------------+---------------------
 2020-07-01 00:00:00 | 2020-07-08 00:00:00
 2020-07-02 00:00:00 | 2020-07-09 00:00:00
 2020-07-03 00:00:00 | 2020-07-10 00:00:00
 2020-07-04 00:00:00 | 2020-07-11 00:00:00
 2020-07-05 00:00:00 | 2020-07-12 00:00:00
 2020-07-06 00:00:00 | 2020-07-13 00:00:00
 2020-07-07 00:00:00 | 2020-07-14 00:00:00
 2020-07-08 00:00:00 | 2020-07-15 00:00:00
(8 rows)

You can then join this to your data table using the begin_date and end_date as limits.

select gs.begin_date, gs.begin_date + interval '7 days', d.* 
  from generate_series('2020-07-01'::timestamp, 
                       '2020-07-15'::timestamp - interval '7 days', 
                       interval '1 day') as gs(begin_date)
       left join my_data_table d
              on d.timestamp_column >= gs.begin_date
             and d.timestamp_column < gs.begin_date + interval '7 days'
;

This will return every row that falls within every begin_date and end_date pairing.

Upvotes: 2

Related Questions