Hi Dale
since I don't know how to open a new subject, I will ask here, since you seem to be an "expert in sql" and I know very "little" of it:
I copied a sql query which I modified for having the results between a certain date and time, but I don't seem to be able to modify the query to get the username, format the the filed time as I have it with the filed desde and what's more... what on earth is delta???? and the time ( tiempo) is minutes? seconds?. I am completely lost:
SELECT
l.id,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%y') AS Desde,
@prevtime := (SELECT MAX(timecreated) FROM prefix_logstore_standard_log
WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS Dtime,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS Tiempo,
l.timecreated-@prevtime AS delta
FROM prefix_logstore_standard_log AS l,
(SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
AND l.timecreated > UNIX_TIMESTAMP('2020-04-01 00:00:00') # optional START DATE
AND l.timecreated <= UNIX_TIMESTAMP('2020-04-14 11:59:00') # optional END DATE
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
since I don't know how to open a new subject, I will ask here, since you seem to be an "expert in sql" and I know very "little" of it:
I copied a sql query which I modified for having the results between a certain date and time, but I don't seem to be able to modify the query to get the username, format the the filed time as I have it with the filed desde and what's more... what on earth is delta???? and the time ( tiempo) is minutes? seconds?. I am completely lost:
SELECT
l.id,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%y') AS Desde,
@prevtime := (SELECT MAX(timecreated) FROM prefix_logstore_standard_log
WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS Dtime,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS Tiempo,
l.timecreated-@prevtime AS delta
FROM prefix_logstore_standard_log AS l,
(SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
AND l.timecreated > UNIX_TIMESTAMP('2020-04-01 00:00:00') # optional START DATE
AND l.timecreated <= UNIX_TIMESTAMP('2020-04-14 11:59:00') # optional END DATE
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%