SQL query for calculating real playing time

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

SQL query for calculating real playing time

Postby Raipe » Tue Feb 17, 2009 5:08 pm

Hi,

I am writing a SQL query for my own report application, but I got stuck when trying to get the value for real playing time. In PT3 the value I am trying to get can be seen on the Sessions tab -> "Sessions - By Time" display -> Minutes column.

In the PT3 database, I can find the minutes count for each session in "holdem_table_session_summary"-table in field "cnt_minutes". That table gives me also the start time and end time of each session. So my question is that how can I write the SQL-query so that it gives me the real time that has been played (removes all overlapping minutes of different sessions)?

Thanks for your help!
Raipe
 
Posts: 4
Joined: Mon Dec 29, 2008 2:31 pm

Re: SQL query for calculating real playing time

Postby kraada » Tue Feb 17, 2009 6:15 pm

You want to use the date_diff() built in postgres function. date_diff(date_end, date_start, 'minutes') +1 will give you the length of a specific session in minutes. If you select for the right date_start and date_end that is. PT3 has built in variables in the Holdem Cash Sessions section for cnt_minutes_real_day cnt_minutes_real_limit and cnt_minutes_real_month which uses an internal lookup function to make sure we select properly in those spots.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query for calculating real playing time

Postby Raipe » Tue Feb 17, 2009 6:51 pm

Thanks for the quick reply.

Yes, I know that date_diff function gives me the time interval of two timestamps, the problem is just like you say to get the right dates to be calculated.. :) I guess I need to do some inner joins with some conditional clauses to get the right dates for the date_diff function, just haven't been able to figure the right query yet.. can you provide some help on how the dates for cnt_minutes_real_month are selected?
Raipe
 
Posts: 4
Joined: Mon Dec 29, 2008 2:31 pm

Re: SQL query for calculating real playing time

Postby kraada » Tue Feb 17, 2009 7:50 pm

I don't have knowledge of the inner working of that function, but I've asked someone who does to take a look at this thread for you.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query for calculating real playing time

Postby Raipe » Sat May 02, 2009 12:50 pm

Hi Kraada,

I am continuing to work on my sql query, your help on this is much appreciated!
Raipe
 
Posts: 4
Joined: Mon Dec 29, 2008 2:31 pm


Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 0 guests