0

There is a table post_status_changes, which is history of post status changes

 post_id |         created_at  | status
---------+---------------------+---------
       3 | 2016-09-02 04:00:00 | 1      
       3 | 2016-09-04 19:59:21 | 2     
       6 | 2016-09-03 15:00:00 | 5      
       6 | 2016-09-03 19:52:46 | 1      
       6 | 2016-09-04 20:53:22 | 2

What I wanna get is a list for each day from DayA till DayB of post status for end of date.

DayA = 2016-09-01
DayB = 2016-09-05

  post_id  | date        | status 
-----------+-------------+---------
         3 | 2016-09-01  | null          
         3 | 2016-09-02  | 1         
         3 | 2016-09-03  | 1  
         3 | 2016-09-04  | 2  
         3 | 2016-09-05  | 2   
         6 | 2016-09-01  | null  
         6 | 2016-09-02  | null 
         6 | 2016-09-03  | 1 
         6 | 2016-09-04  | 2 
         6 | 2016-09-05  | 2

Any solutions?

Grosefaid
  • 138
  • 1
  • 7

3 Answers3

0

solution was found here: PHP: Return all dates between two dates in an array

$period = new DatePeriod(
     new DateTime('2010-10-01'),
     new DateInterval('P1D'),
     new DateTime('2010-10-05')
);

foreach ($period as $each){
   //.. QUERY here, where "CREAtED_AT" = $each
}
Community
  • 1
  • 1
T.Todua
  • 53,146
  • 19
  • 236
  • 237
  • some sql solution? – Grosefaid Sep 16 '16 at 16:10
  • about sql, i cant say. If you have the only way to do that using sql, that in my mind comes only the manual way to create the sql which contains all dates in the query, and that can be done automatically using javascript bookmarks, to fill the SQL input field with the correct SQL command. but you need to do some more research in that case, to get code like: `javascript:function(){.....final = date1 + date2...};` if you dont understand what i say, then you can disregard this my comment at all. – T.Todua Sep 16 '16 at 19:12
0

For each post_id you want as many rows as there are days between the start and end date. This can be done by cross joining the list of dates with the post_ids and then join that result back to the table to get the status for each day:

select x.post_id, t.created, p.status
from generate_series(date '2016-09-01', date '2016-09-05', interval '1' day) as t(created)
  cross join (
     select distinct post_id 
     from post_status_changes
  ) x
  left join post_status_changes p on p.created_at::date = t.created
order by 1,2;

Running example: http://rextester.com/CSX38222

0
with a as 
    (select convert(varchar(10), created_at, 102) [date], [status], 
    post_id, rank() over (partition by convert(varchar(10), created_at), 
    post_id order by created_at desc) as r 
    from post_status_changes)
select post_id, [date], [status] from a where r = 
    (select top 1 r from a as a2 where a.[date] = 
    a2.[date] and a.[post_id] = a2.[post_id])
    and @DayA <= [date] and @DayB >= [date] order by post_id, [date];
  • This is invalid for Postgres (there is no `convert()` function, and square brackets are invalid in an identifier). You should at least use standard SQL for a question tagged with `sql` –  Sep 16 '16 at 20:01
  • And how does it generate the rows for 2016-09-01 or 2016-09-05 which are not present in the table? –  Sep 16 '16 at 20:15
  • Since I am new to answering questions on stack overflow, I overlooked that this was categorized under the postgresql tag. I was using T-SQL and also neglected to generate the series where the elements are not in the table. I found information on generating a series in T-SQL here, just in case anyone is interested, even though I should be answering questions according to the tag they are categorized under: http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 –  Sep 17 '16 at 01:52