Hours Played Per Week

[IceAge]

Hello, I would like to make a stat that displays how many hours I've played in a week. Currently I'm using the "group by week" report from the download warehouse. The report I'm using looks like this: https://gyazo.com/ad99f8e8c048c03952fff0ac1dc95f2d

Most of the stats work for weekly grouping, except the time related ones.

Thanks for any assistance.

[kraada]

Unfortunately I don't think it's going to be possible to do this out of the box without some fairly advanced SQL - the groupings for date especially if you want multitabling to be accounted for are somewhat complex.

[IceAge]

Hmm OK. Well, suppose I know that I can always play 6.5 games per hour. Is it possible to do something like "total minutes played this week divided by 60 divided by 6.5" to approximate hours spent multitabling in a week?

[kraada]

Table minutes should be doable.

[IceAge]

Could you offer some guidance on how to do this please? I'm still an amateur at these stats. I tried the following expression and it (obviously?) didn't work: (var_val_minutes_real / 60) / 6.5

That just listed a separate entry for each... Tournament I played? See here: https://gyazo.com/0853c9e389d847a1aaef68e7f380db1f

[kraada]

Yeah unfortunately that version is grouped by tournament - you'd need to write the direct SQL on this one which is still complex. I'll put it on my list to work on when I get a chance for you.

[IceAge]

Ok then. Thanks for your help. If you really want to challenge yourself, make me an hours played per week (multitabling) stat I don't really need the second one, but certainly if it's a choice between that and nothing at all, I'll be happy to get whatever I can!

[BillGatesIII]

If you know how to create a function, you might use this piece of code.

Code: Select all

CREATE OR REPLACE FUNCTION tourney_hours_played_yearweek(
  yearweek integer)
RETURNS double precision AS
$BODY$
  select sum(x.seconds_played) / 3600
  from (
    select
      case
        when coalesce(extract(epoch from ts.date_start - max(ts.date_end) over (order by ts.date_start, ts.date_end rows between unbounded preceding and 1 preceding)), 0) >= 0
        then extract(epoch from ts.date_end - ts.date_start)
        else greatest(0, coalesce(extract(epoch from ts.date_end - max(ts.date_end) over (order by ts.date_start, ts.date_end rows between unbounded preceding and 1 preceding)), 0))
      end as "seconds_played"
    from tourney_summary as ts
    join tourney_hand_summary as ths on ths.id_tourney = ts.id_tourney
    where to_char(ts.date_start + INTERVAL '-10 HOURS', 'IYYYIW')::int = yearweek
  ) as x
$BODY$
LANGUAGE sql STABLE
COST 100;
ALTER FUNCTION tourney_hours_played_yearweek(integer)
OWNER TO postgres;

The column.

Code: Select all

tourney_hours_played_yearweek(to_char(datefix_fromutc[tourney_summary.date_start], 'IYYYIW')::int)

Keep in mind that filtering messes things up. Also if you play a tourney that starts Sunday and ends somehow Monday afternoon, it will count the hours in the week of the Sunday.

[IceAge]

Thank you very much for this information! Unfortunately, I don't see any way to create a function in PT4. Is there a forum post or something I can take a look at?

[kraada]

This is done in PostgreSQL directly via PGAdmin III or psql command line.