Data Explorer (SEDE)

1:40 AM

Recently posted on MathOverflow Meta: In the MathOverflow data, can you determine the number of hours you've spent on MathOverflow?

1

Q: In the MathOverflow data, can you determine the number of hours you've spent on MathOverflow?

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?

support

I saw some similar posts on other sites:

Some posts which are at least a bit similar: How many hours I spend on SO on Meta StackOverflow, To estimate how much time I've spent on SE, what tools are available to help, or at least how can I "vacuum up" all of my timestamps? (on Meta Stack Exchange) and How many hours has a user been on math.stack? (on Mathematics Meta). — Martin Sleziak 29 mins ago

-14

Q: How many hours I spend on SO

Is there a way to find how many hours I spent on SO. Also who has spent most number hours on SO(probably Jon Skeet ;)). Is there such option available. If No then adding will be good. What you guys say?

discussion support feature-request

9

A: How many hours I spend on SO

Until last Sunday you have lost 2892 hours of your life on Stack Overflow based on a very rough, non-statistical sound approach using data available in SEDE Here is the query: with timeevents as ( select creationdate , len(body) as length , case when posttypeid = 1 then 'Q' ...

If I take the query from that post as a basis, perhaps I could count number of distinct days (or hours) with at least on event (i.e., a comment or a post).

Testing: data.stackexchange.com/mathoverflow/query/1815665/…

Results for an individual user: data.stackexchange.com/mathoverflow/query/1815666/…

On a small site, one could collect the data for all users: data.stackexchange.com/mathoverflow/query/1815667/…

One would expect number of (distinct) minutes and number of all actions to be similar.

Martin

5:30 AM

0

A: In the MathOverflow data, can you determine the number of hours you've spent on MathOverflow?

Here are some similar questions on other sites: How many hours I spend on SO (Meta StackOverflow) To estimate how much time I've spent on SE, what tools are available to help, or at least how can I "vacuum up" all of my timestamps? (Meta Stack Exchange) How many hours has a user been on math.sta...

Feeds

6:50 AM

2

Q: In the MathOverflow data, can you determine the number of hours you've spent on MathOverflow?

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?

support statistics users profile-page data-explorer

rene

7:24 AM

@Martin that ... is creative. here are some other alternatives: data.stackexchange.com/mathoverflow/query/1815675

DateTimes are tricky because in some situations a server datetime setting might return different results for the same query. Not a problem within SEDE but something to keep in mind if you apply that trick on a SQL database that has a different setting.

Martin

8:17 AM

I took the approach suggested in one of the answers here: How to select date without time in SQL. I.e., the date converted as: convert(varchar(10), CreationDate, 120).

365

Q: How to select date without time in SQL

When I select date in SQL it is returned as 2011-02-25 21:17:33.933. But I need only the Date part, that is 2011-02-25. How can I do this?

sql sql-server sql-server-2005

182

A: How to select date without time in SQL

I guess he wants a string. select convert(varchar(10), '2011-02-25 21:17:33.933', 120) 120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.

What is 120 for? — Павле Jan 20, 2020 at 12:57

@Павле refer this article Date and Time styles — Fox Vĩnh Tâm Feb 26, 2020 at 9:04

Certainly, when I have a bit of time, I will check the various alternatives you suggested.

select top 1
       creationdate
       , convert(date, creationdate) [convert to date]
       , convert(nvarchar, creationdate, 127) [with style] -- learn.microsoft.com/en-us/sql/t-sql/functions/…
       , format(creationdate,'yyyy') [format year]
       , format(creationdate,'yyyy-mm-dd') [format date]
       , format(creationdate,'hh:MM:ss:fff') [format time]
from posts

rene

@Martin ah, yes I missed the 120 there. That forces the right format.

Martin

I should keep in mind that there is format(creationdate,'yyyy'), format(creationdate,'yyyy-mm-dd') and format(creationdate,'hh:MM:ss:fff'). Actually, I think I have used something like this before - but forgotten about it since then.

rene

also note that format is a relatively recent addition to T-SQL. It is kind-of convenient for me because it is the same as what the .NET Framework offers in the ToString formatting for datetime type. And C# / .Net is my "native" programming language.

Martin

I have used 'yyyy-MM' in this query - but after that I started using eomonth when I needed to group something by month.

I should probably bookmark this conversation - and return back to this at some point.

Martin

10:24 AM

1

A: In the MathOverflow data, can you determine the number of hours you've spent on MathOverflow?

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...

This is only a minor issue, but since this uses badges table too, it counts also obtaining badges as an event - to get a badge one doesn't really have to visit the site.

rene

Yeah, I thought about that but decided to not rule it out. Some badges are linked to a user doing things ....

Would the # of minutes really drop down significantly in that case?

if you had only one event on a day due to a badge it doesn't in minutes anyway.

It was bad enough I had to do the Posthistory voters after the fact. Which isn't time correct either.

I'll accept that will hurt your Math brain

Martin

One think isn't clear to me - if there are several events on the same day, doesn't the query count this day multiple times?

For example, if somebody visited the site only once, posted 1 question and two comments - will the result be one or three?

rene

no, it does a group by on the day. So each day is only present once

In your example there would be one row

Martin

This user has one question and two comments and your query returns one day.

I see, this part is relevant for my question: group by convert([date], date).

Still, I was a bit surprised by the difference between two queries - 850 days vs. 1644 days.

But my query only counts posts and comments, so it is probably believable that including various other things increases the number of days significantly.

rene

10:42 AM

Yeah, I think badges and the posthistory voters stuff is most likely to cause higher days count. That is a valid point of concern

Martin

For me your query returns: 1986 days on MO and 1422 days on meta.MO.

And my query returns: 671 days on MO and 1110 days on meta.MO.

Calendar shows me the number of visited days: 4332 on MO and 3275 on meta.MO.

But the number in the calendar is expected to be higher - people often visit the site without doing any action on that day (just browsing, searching, reading).

rene

I've made a clarification edit why the days might be inaccurate in my query.

@Martin visited is counted by SE as: loaoding SE (assuming you're logged in) and then clicking once to any other page.

Martin

Ok, it's time for lunch. See you later!

Transcript for

Data Explorer (SEDE)