8

I just wrote the following comment:

While SE isn't social media per se I've asked about 1,500 questions in SE, and I could probably reconstruct a metric for total hours spent if I applied some algorithm to the times and clusterings of all my posts and comments. I fear what the results would show!

(I'm up to about 5,000 questions now)

Is there a way I can 'vacuum up" all of the timestamps of all of my question and answer posts and comments, sorted only by site and type, so that I could attempt such a cluster-finder and time estimator?

I know a little python but I'm not a developer, so an answer with a bit of explanation is greatly appreciated. I'm only looking for solutions that I can implement for myself, it wouldn't need it to work on other users' activities. In fact it would be best if it couldn't.

7
  • 4
    You can use the Stack Exchange Data Explorer (SEDE) to query all your posts and their timestamps (including time posted, edited, etc). I think it may also include comments, but am unsure. There are different catalogs for different sites in Stack Exchange, so you’ll have to do some meta-SQL if you participate in a lot of sites.
    – Dan Bron
    Nov 11, 2018 at 2:41
  • @DanBron Thanks. I guess that would start with learning SQL? Right now my only computer skills are Python. For example, I have run this script but I didn't necessarily know what I was doing.
    – uhoh
    Nov 11, 2018 at 2:48
  • 2
    SQL isn’t that bad for simple stuff. Basically all you need to do is query a single table filtered by your user ID. If you want to get fancy you can join a couple tables to get more detailed timelines of each post. It’s an online, easy to use sandbox, and you can’t break anything. It’s a good way to learn.
    – Dan Bron
    Nov 11, 2018 at 2:50
  • @DanBron okay, with that confidence-booster, I will give it a try. I like it when I can't break anything.
    – uhoh
    Nov 11, 2018 at 2:51
  • 2
    @uhoh SEDE has a tutorial that you might find helpful. Nov 11, 2018 at 21:33
  • @MonicaCellio excellent, I'll review that now. Thanks! So far I only get one line returned but it's probably only a matter of time before I figure out how to actually run the script.
    – uhoh
    Nov 12, 2018 at 2:11
  • 1

1 Answer 1

4
+50

This query, will show the timestamps on a single site for questions, answers, favoriting starting bounties and comments. Important note: as of October 2022, Favorites are no longer stored in SEDE, but there are still the records up to that change. For more details see Bookmarks have evolved into Saves and What does replacing bookmarks with Saves mean for SEDE?.

SELECT creationdate,'Question'
FROM posts
WHERE posttypeid = 1 
AND
owneruserid = ##UserId:int##

UNION

SELECT creationdate,'Answer'
FROM posts
WHERE posttypeid = 2 
AND
owneruserid = ##UserId:int##

UNION

SELECT creationdate,'Favorite'
FROM votes
WHERE VoteTypeId  = 5  
AND
userid = ##UserId:int##

UNION

SELECT creationdate,'Bounty Start'
FROM votes
WHERE VoteTypeId  = 8 
AND
userid = ##UserId:int##

UNION

SELECT creationdate,'Comment'
FROM comments
WHERE userid = ##UserId:int##
ORDER BY creationdate

It would take more work to run this across multiple sites, but it should be a good starting point for you. Of course, comments get deleted after a while, so that information is probably less useful

2
  • Thank you, this makes the basic syntax clear. Rather than your link with the placeholder comment for userid, I've pasted a copy with my actual user id integer on a particular site into Compose Query and entered the url for that site in the window for url at the bottom, but still get only one line return. I will try to read the tutorial to see how to actually use this, and leave a comment when I'm successful.
    – uhoh
    Nov 12, 2018 at 2:11
  • 1
    @uhoh The advantage of using parametrs (rather then writing down the specific number), is that this can be easily used for several users or for the same user on different sites. Have a look at this, this and this. The SEDE tutorial has a section on parameters.
    – Martin
    Jan 24 at 6:28

You must log in to answer this question.

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