4

This looks like the Question was posted After the Answer, which isn't possible.

This should swap around. (link to timeline)

closed as off-topic by nicael, S.L. Barth, Shadow The Curly Braced Wizard, ale, Ward Jan 13 '16 at 17:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "The problem described here can no longer be reproduced. Changes to the system or to the circumstances affecting the asker have rendered it obsolete. If you encounter a similar problem, please post a new question." – nicael, S.L. Barth, Shadow The Curly Braced Wizard, ale, Ward
If this question can be reworded to fit the rules in the help center, please edit the question.

  • No repro, it's OK now. Maybe editing fixed something behind the scenes? :) – Shadow The Curly Braced Wizard Apr 25 '15 at 16:20
  • @ShadowWizard Yea, fixed now... – Tim Apr 25 '15 at 16:21
  • Yup, definitely fixed now (at least everything's fine with the new timeline). – nicael Jan 13 '16 at 15:31
  • i saw they updated the css a bit @nicael – Tim Jan 13 '16 at 15:59
6

As that timeline is not supported and it might be removed without any prior notice I created this query that mimicks the data also present in the timeline. As this runs in SEDE the data is up to an week old as it is refreshed on Monday morning.

declare @qid int = ##questionid##

;with qs as (
select creationdate
     , id
     , title
     , body
     , owneruserid
     -- , lastactivedate
from posts 
where id = @qid
)
, ans as (
select creationdate
     , id
     , body
     , owneruserid
     , parentid
   --  , lastactivedate
from posts 
where parentid = @qid
)
, posttype as (
select id
     , case when p.posttypeid = 1 
            then 'Question'
            else 'Answer'
       end as qa
       , 'site://questions/' 
       + cast(id as nvarchar) 
       + '|'
       + case when p.posttypeid = 1 
            then title
            else left(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(body
            ,char(13),' ')
            ,char(10),' ')
            ,':',' ')
            ,'<p>','')
            ,'</p>','')
            ,'<strong>','')
            ,'</strong>','')
            ,'<h2>','')
            ,'</h2>','')
            ,'<pre','')
            ,'</pre>','')
            ,'<code>','')
            ,'</code>','')
            ,'<blockquote>','')
            ,'</blockquote>','')
            , 40)
         end
         as url
from posts p
where id = @qid or parentid= @qid
)
, cmts as (
select c.creationdate
     , c.id
     , userid
     , [text]
     , p.qa
     , p.url
from comments c
inner join posttype p on p.id = c.postid
)
, edits as
(
   select se.id
        , p.url
        , se.creationdate
        , se.owneruserid
        , se.comment
   from suggestededits se
   inner join posttype p on p.id = se.postid
   where approvaldate is not null
)
, voting as 
(
  select dateadd(s, -1, dateadd(d, 1, v.creationdate)) as creationdate
       , p.url
       , count(*) as cnt
       , case 
         when votetypeid = 2 then 'UP'
         when votetypeid = 3 then 'DOWN'
         end as votetype
  from votes v
   inner join posttype p on p.id = v.postid
   where votetypeid in (2,3)
   group by dateadd(s, -1, dateadd(d, 1, v.creationdate))
   , p.url
   , votetypeid
)
, accept as 
(
  select dateadd(s, -1, dateadd(d, 1, v.creationdate)) as creationdate
       , p.url
       , count(*) as cnt
       , 'ACC' as votetype
  from votes v
   inner join posttype p on p.id = v.postid
   where v.votetypeid = 1
   group by dateadd(s, -1, dateadd(d, 1, v.creationdate))
   , p.url
)
, bounties as 
(
  select dateadd(s, -1, dateadd(d, 1, v.creationdate)) as creationdate
       , p.url
       , userid
       , case 
         when votetypeid = 8 then 'BS'
         when votetypeid = 9 then 'BE'
         end as votetype
       , bountyamount
  from votes v
   inner join posttype p on p.id = v.postid
   where v.votetypeid in (8,9)
)
, fav as 
(
  select dateadd(s, -1, dateadd(d, 1, v.creationdate)) as creationdate
       , p.url
       , userid
       , 'FAV' as votetype
  from votes v
   inner join posttype p on p.id = v.postid
   where v.votetypeid = 5
)
, mut as 
(
   select ph.creationdate
        , case 
          when posthistorytypeid = 4 then 'ETI'
          when posthistorytypeid = 5 then 'EBO'
          when posthistorytypeid = 6 then 'ETA'
          when posthistorytypeid = 7 then 'RTI'
          when posthistorytypeid = 8 then 'RBO'
          when posthistorytypeid = 9 then 'RTA'
          end as type
        , p.url
        , ph.userid
        , ph.comment
   from posthistory ph
   inner join posttype p on p.id = ph.postid
   where posthistorytypeid between 4 and 9
)
-- start union-ing all cte's
select creationdate
    ,  'Q' as event
    , owneruserid as [User Link]
    , 'site://questions/'+ cast(id as nvarchar)+ '|Question' as [QA Link]
    , null as [Votes or Comment - Edit Link] 
from qs
union
select creationdate
    ,  'A'
    ,  owneruserid as [User Link]
    , url
    , null
from ans
inner join posttype pt on pt.id = ans.id
union 
select creationdate
     , 'C'
     , userid as [User Link]
     , url
     , 'site://posts/comments/'
     + cast(id as nvarchar)
     + '|' 
     + [text]
from cmts
union
select creationdate
     , 'E'
     , owneruserid
     , url
     , 'site://review/suggested-edits/' 
     + cast(id as nvarchar)
     + '|' 
     + comment
from edits
union
-- voting!
select creationdate
     , votetype
     , null
     , url
     , cast(cnt as nvarchar)
from voting
union 
-- Accept!
select creationdate
     , votetype
     , null
     , url
     , cast(cnt as nvarchar)
from accept
union
-- Bounties
select creationdate
     , votetype
     , userid
     , url
     , cast(bountyamount as nvarchar)
from bounties
union
-- Favorite
select creationdate
     , votetype
     , userid
     , url
     , ''
from fav
union
-- Mutation
select creationdate
     , type
     , userid
     , url
     , comment
from mut
order by creationdate desc

When this is run for specific question your result will look like this: timeline output in sede

I take records from the Posts, comments, suggested-edits, votes and posthistory tables and union them all based on creation date. The votes table only has the precision of a day but the others include the time as well. Where possible I link to the edit or comment. For up- and downvotes I show the number of votes per day per post. For bounties the bounty amount is present.

If you want to add more recordtypes all what is needed is an extra cte and an union. I leave that as an exercise for the reader

  • Does this have the 1-2 days delay? – Tim Apr 25 '15 at 19:39
  • No, 7 days delay. SEDE is updated on Monday morning – rene Apr 25 '15 at 19:40
  • Oh okay :) Still, better than nothing so +1! – Tim Apr 25 '15 at 19:40
  • @rene up to 7 days delay - when the data is fresh, the delay can be few minutes, no? :-) – Shadow The Curly Braced Wizard Apr 25 '15 at 20:17
  • @ShadowWizard Huh....Hmmmmm, yes... so...? – rene Apr 25 '15 at 20:18
  • Nothing, just trying to make things as accurate as possible for @Tim sake. :) – Shadow The Curly Braced Wizard Apr 25 '15 at 20:20
  • Yeah :P Help the noob programmer ;) – Tim Apr 25 '15 at 20:20
3

This is a very old view, which is not maintained nor supported anymore.

It might go any day.

  • 1
    If it did could we have a replacement? Maybe an easily accessible one? Or will this info be lost in the depths of SQL servers? – Tim Apr 25 '15 at 16:22
  • @Tim guess we can make a SEDE query to mimick the most useful feature of this, which is amount of votes per day. Maybe revisions too. I know someone who doesn't back away from complicated SEDE queries... ;) – Shadow The Curly Braced Wizard Apr 25 '15 at 16:26
  • 1
    I have also seen this in search results, where the posts are sorted from newest to oldest, but the question is listed before the answer for self-answered questions. – Reto Koradi Apr 25 '15 at 18:53
  • @RetoKoradi If you see it again then that is a current bug! i.imgur.com/zkOJG7A.png – Tim Apr 25 '15 at 19:43

Not the answer you're looking for? Browse other questions tagged .