12

To preface, I'm not asking for vote time to be exposed for upvotes or downvotes. I'm aware that that's bad because it allows voting history to be reconstructed.

However, certain other types of votes already have their times publicly exposed elsewhere but not in the Data Explorer. In particular:

  • The time of an accept (VoteTypeId = 1) can be seen at /posts/<id>/timeline.
  • The time of a bounty start or a bounty award (8, 9) is visible at /posts/<id>/revisions.
  • The time at which an edit is approved (16) is also visible at /posts/<id>/revisions.

It would be awesome if the times at which these types of votes were cast could be added to the Votes table. (By which I mean, instead of having CreationDate always be yyyy-mm-dd 00:00:00, have actual times for these types of votes.)

Motivation: mostly, I want to look at the duration of time between an answer being posted and it being accepted, and see how that correlates to other things about the question/answer. So I'm mostly interested in type 1 votes; the others I've included here just for completeness. Jeff mentions that adding accept times to the data dump would not be objectionable to him in a post from 2010, but it doesn't look like anything came of that.

1

This is not a complete answer but addresses two of your requests.

The exact accept time can't be known.

For approved edits you can inspect the table suggestededits. The column approvaldate will hold the exact timestamp the suggestededit was approved.

For the exact timestamp on all edits on a post you can look at posthistorytypeid for the rows with posthistorytypeid values of 4, 5 or 6. (title, body and tag edits events), the creationdate will have the exact timestamp.

For bounties you can look at the creationdate en deletiondate of PostNotices. You need to join with PostNoticeTypes because the rows with classid = 2 indicate a bounty. This query produces that data:

select top 100 pn.id
          , pn.postid
          , pn.creationdate
          , pn.deletiondate
          , v.creationdate [vote date]
          , v.votetypeid   [vote type]
from postnotices pn
inner join votes v on v.postid = pn.postid 
inner join postnoticetypes pnt on pnt.id = pn.postnoticetypeid
                              and pnt.classid = 2 -- bounty
where (v.votetypeid  = 8 
and v.creationdate = convert(date, pn.creationdate))
or (v.votetypeid  = 9 
and v.creationdate = convert(date, pn.deletiondate))

order by pn.postid

You must log in to answer this question.

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