Know that the information you are looking for is stored in the USERLOG table, at the SQL level.
Also, know that the WAY this data is stored in the USERLOG table can vary, depending on one specific GLOBAL setting in GoldMine. This GLOBAL setting is the "Time Clock" setting on the Login tab of any GM user's OPTIONS.
This query will give you some quick exposure to the data itself:
select * from userlog
Know that you can use the SQL cast() function to make the login and logout columns truly behave like date and time stamps, as illustrated here:
select cast(login as datetime), cast(logout as datetime), * from userlog
The DUR column is the # of minutes for that logged entry (that row of data in the userlog table), which may be for a single login/logout session OR may be totals for a day, depending on the "Time Clock" setting noted above.
Regarding "unless the query can add time logged in"...MOST ASSUREDLY. You should be able to manipulate as you see fit (with the correctly written SQL query, of course).
Here is a query, just as an example, that totals the minutes for a given date range (using the SQL sum() function), calculates # of hours AND counts the number of logged entries for that same date range, grouping by GoldMine user ID:
select userid, sum(dur) as 'Total Mins', cast(sum(dur)/60.0 as float) as 'Total Hours', count(*) as 'Count' from userlog where cast(login as datetime) between '7/1/2016' and '7/31/2016' group by userid
Hope this feedback helps. Please let me know if I need to clarify anything.
Thank you for your response. I have been able to get the data I need with the queries above. If our time clock settings are set to track each login, would the last query above still produce an accurate total?
Yes, that query should be accurate, for the date range in question, no matter which Time Clock setting is being used (other than the "Disable Time Clock" setting, of course).
If you wanted DAILY totals for yourself, then you could add the login date to the query and group by clause, like this:
select userid, cast(login as datetime) as 'Date', sum(dur) as 'Total Mins', cast(sum(dur)/60.0 as float) as 'Total Hours', count(*) as 'Count' from userlog where cast(login as datetime) between '7/1/2016' and '7/31/2016' group by userid, cast(login as datetime)
Glad it was helpful.
This was another really useful query. Thank you John.