Difference between Time value and System Time

[jordan1]

Hello,

I'm developing a dashboard which currently tracks the time spent on a building site. Some of the data automatically collected for this is the Check In Time and Check Out Time. From this, we calculate a value for Minutes on Site, using Check Out Time - Check In Time. However, this means that we cannot get a value for Minutes On Site while a person is still on site, as they do not have a Check Out Time.

How do I create a column on a table which displays Time (Now) - Check In Time in minutes?

The Check In Time is formatted as below;

"in_time_num",TO_CHAR(TO_DATE(OT."In Date", 'DD/MM/YYYY HH24:mi:SS'), 'HH24:mi') as IN_DATE_TIME, 

Minutes on Site is calculated as below;

ROUND((to_DATE(OT."Out Date",'DD/MM/YYYY HH24:mi:SS') - to_DATE(OT."In Date",'DD/MM/YYYY HH24:mi:SS')) * 24 * 60) AS "Minutes On Site",

Thanks,
Jordan

[Or · Accepted solution]

Depending on your specific DB the syntax may change, but it looks like Oracle, so you would use Sysdate. That said, this isn't a Qlik question at all... in Qlik, the syntax would be Now(), but Qlik doesn't have a TO_DATE function.

ROUND((to_DATE(OT."Out Date",'DD/MM/YYYY HH24:mi:SS') - SYSDATE) * 24 * 60) AS "Minutes On Site",

[jordan1]

Thanks! This sort of worked (the wrong way round).