title: Can somebody help me translate this into postgresql?
body: <p>I am very new to SQL and I do not know much about writing code in the different DBMS. I am trying to write a report in our school's MOODLE platform, which uses postgresql, using a configurable report found <a href="https://docs.moodle.org/39/en/ad-hoc_contributed_reports#User.27s_accumulative_time_spent_in_course" rel="nofollow noreferrer">here</a>. However, the code does not work in postgresql. In particular, how do I rewrite those lines with variable assignments like <code>@prevtime :=</code> to make the code work in postgresql?</p>
  <p>Here is the complete code from the link.</p>
  <pre><code>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 &lt; l.id ORDER BY id ASC LIMIT 1) AS prev_time,
  IF (l.timecreated - @prevtime &lt; 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
  l.timecreated-@prevtime AS delta,
  &quot;User&quot; 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:&gt;%% %%FILTER_ENDTIME:l.timecreated:&lt;%%
  </code></pre>
  <p>This is supposed to report the time spent by students in courses in MOODLE.</p>