Hi all. Sorry if this looks like a newbie problem, because I am a newbie to Moodle and SQL in general. I saw this contributed SQL report written in some SQL DBMS. Now I learned that our Moodle installation uses postgresql and this code doesn't work.
SELECT l.id, l.timecreated, DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y') AS dTime,
@prevtime := (SELECT MAX(timecreated) FROM mdl_logstore_standard_log WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS prev_time,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
l.timecreated-@prevtime AS delta, "User" AS TYPE
FROM prefix_logstore_standard_log AS l, (SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
I would appreciate it if somebody can guide me through re-writing this in postgresql. Thanks in advance!