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:
SELECT TO_CHAR(l.logindate, ‘MM/DD/YYYY’) logindate,
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
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
It takes a raw seconds field and then converts it to corresponding hours, minutes, and seconds.
Leave a Reply