Converting seconds to time in PowerSchool custom pages

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
It takes a raw seconds field and then converts it to corresponding hours, minutes, and seconds.

2 responses to “Converting seconds to time in PowerSchool custom pages”

  1. PowerSchool’s tlist_sql has trouble with certain special characters. Among them is colons. You have to adjust the time mask to not include actual colons. You can do this by building a composite of the mask by concatenating the non-colon values with chr(58) — 58 is the ASCII decimal for a colon:

    e.g. ‘hh24:mi:ss’ needs to be changed to something like ‘hh24’||chr(58)||’mi’||chr(58)||’ss’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.