5
$\begingroup$

In your profile, you can find a list of all your questions, answers, and comments, and also how many total days you've been logged into MathOverflow. Is there also a way to determine the total number of hours you've been active? If not on your profile, then perhaps in the MO Data Explorer?

$\endgroup$
3

2 Answers 2

Reset to default
4
$\begingroup$

I've tried to make this as complicated as possible by looking in the information_schema.columns to find all tables that have either a userid or owneruserid column and a date or creationdate column.

With that data I can construct an dynamic SQL query to gets for a specific userid the relevant date data from each table. I had to add a special case for PostHistory as that encodes userid in a JSON format that needs special handling.

Once we have all events we can group on single days (UTC /SE Day is assumed here) and then obtain the number of minutes by the difference between min(date) and max(date) for that day. If there is only one event on that day, you're out of luck, no minutes for you!

Finally we sum all minutes so we can get hours and days from that.

Here is the SEDE query:

declare @sql nvarchar(max)

-- temp table to hold all events
create table #events ([date] datetime, [name] sysname);

-- find all tables that either have a userid or an owneruserid
;with tables as (select table_name, column_name
from information_schema.columns
where column_name in ('userid', 'owneruserid')
),
-- now only use tables that also have a creationdate or date 
tables_data as (
select c.table_name
     , t.column_name [user_col]
     , c.column_name [date_col]
from information_schema.columns c
inner join tables t on t.table_name = c.table_name
where c.column_name in ('creationdate', 'date')
)

-- build a dynamic sql statement to get actual dates for that userid
-- store in the temp table
select @sql = 'insert into #events '+ string_agg(concat(N'select ', 
          date_col, N' [date], ', 
   N'''', table_name, N''' [table] 
from ', table_name, '
where ', user_col, N'= ', ##userid##),
N' union all ')
from tables_data

print @sql
exec(@sql)

-- posthistory has Voters info encoded in the text column for some types 
-- the creationdate will not match when the Voter voted so it only gives
-- a rough event range but with enough records it might give some signal
-- above noise level
insert into #events
select creationdate, 'PostHistory'
from posthistory ph
cross apply openjson(ph.text, '$.Voters') WITH(Id int) v
where posthistorytypeid in (10,11,12,13,14,15,19,20,35)
and ph.userid <> ##userid##
and v.Id = ##userid##

select count(*) [# days visited]
     , sum(minutes) [# minutes]
     , datediff(hh, getdate(), dateadd(n, sum(minutes), getdate())) [# hours]
     , datediff(d, getdate(), dateadd(n, sum(minutes), getdate())) [# days]
from (
-- note that days with only 1 event will result in 0 minutes counted
select convert([date], date) [event_day]
     , min(date) [min]
     , max(date) [max]
     , datediff(n, min(date), max(date)) [minutes]
     , count(*) [# events]
from #events
group by convert([date], date) 
--order by 1
) events_per_day

And here is the result for you today:

enter image description here

Thanks to Martin for bringing the question to the SEDE room. In our discussion we've established that the # days visited in my query is substantially higher due to the inclusion of badges and PostHistory voters data as those are more likely to be not tight (or tied, both can work) to the same day an activity took place. For some badges that is even more unlikely.

With this data we can do some trend graphs as well:

enter image description here
click image for query

Keep in mind SEDE is updated once a week on Sunday.
Use the fine SEDE Tutorial written by the admirable Monica Cellio.
Say "Hi" in SEDE chat.

$\endgroup$
0
3
$\begingroup$

Here are some similar questions on other sites:

SEDE does not contain the data about the times you visited the site - not even about the visited days which you can see in your profile. (See also: Add the "Days Visited" metric to SEDE.)

However, SEDE contains the data about various actions you made - for example, you could get timestamps of your posts, comments and some other actions. This could be used for some statistics - naturally, people spend a lot of time on the site by reading, searching, etc.; so this is rather different from getting the total time actually spent on the site.

Using the approach similar to this query (which is taken from rene's answer on Meta StackOverflow), you could get a list of all times when you made posts and comments - and you can then use that data in whichever way you prefer. For example, you can find the number of days, hours, minutes. when you took one of those actions. (Naturally, it is expected the number of minutes will be similar to the total number of posts and comments. One does not write more than one comment per minute too often.)

You can have a look at the same data for various users. Of course, it is possible to run the same query on other sites, such as MathOverflow Meta, Mathematics, Mathematics Meta. The database for Stack Overflow is probably too big, running the query for all users might time out.

Here you can find ab explanation of how days are used in this database: What is an SE "day"? When does each day start? In short, it uses UTC - so depending on your timezone, it is possible that two of your actions fall into two different days in the database even if in your local time they were on the same day.

If you see that there are many days (hours) next to your name, you can view this information positively (since you see that you are often here and you know that your activities here are useful). On the other hand, some people might see this as a negative sign - that they spend too much here instead of other stuff that they are supposed to be doing. (Me writing this answer is an example of this.)

I will add links to some related discussion from elsewhere:

$\endgroup$
0

You must log in to answer this question.

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