I know someone’s going to jump in here and tell me that PowerSchool’s custom pages have no problems with SQL statements but are just less forgiving of syntactical errors than SQL Developer is, but I couldn’t get any of the convert-seconds-to-time standard SQL approaches to work in a PowerSchool custom page using tlist_sql, even when those solutions worked fine in SQL Developer. Here are some examples.
Eventually, the solution that did end up working best was taking a trunc / mod approach in combination with lpad to get some times together. Here is what it looks like as part of the select statement:
lpad(trunc(l.logintime/3600), 2, ’00’) loginhour,
lpad(trunc(mod(l.logintime, 3600)/60), 2, ’00’) loginminutes,
lpad(mod(l.logintime, 60), 2, ’00’) loginseconds
FROM logins l
WHERE l.logindate > sysdate -21
ORDER BY l.logindate, l.logintime