I have a scenario to calculate difference of timestamp in minute. There is one common field in 2 tables i.e dw_job_id and want to join 2 tables and guessing, calculate
(scratch.COGIPF_RUNREPORT_test2.end_ts - concat(proct_dt, scratch.dw_job_sla_dim_test.sla_time)
Example, Subtraction in minute:
(2018-03-05 01:53:14.201000 - 2018-03-05 08:00:00.000000)= -366
It would be quite clear using below records:
1st query has end_ts:
sel * from scratch.COGIPF_RUNREPORT_test2 where dw_job_id=1226
Result:
dw_job_id proct_dt start_ts end_ts time_diff dw_job_status_id
1,226 2018-03-05 00:00:00 2018-03-05 01:50:23 2018-03-05 01:53:14.201000 3 12
1,226 2018-03-06 00:00:00 2018-03-06 01:42:56 2018-03-06 01:45:23.553000 3 12
2nd query :
select * from scratch.dw_job_sla_dim_test where dw_job_id=1226
Result:
dw_job_id sla_hour sla_minute sla_time
1,226 8 0 08:00:00.000000
Final result should be:
dw_job_id run_date start_timestamp end_timestamp runtime_minutes sla_miss_minutes
1,226 3/5/2018 3/5/2018 01:50:23.000000 3/5/2018 01:53:14.201000 2 -366
1,226 3/6/2018 3/6/2018 01:42:56.000000 3/6/2018 01:45:23.553000 2 -374
Example:
Subtraction in minute :-(2018-03-05 01:53:14.201000 - 2018-03-05 08:00:00.000000)= -366
Data types:
sla_hour INTEGER,
sla_minute INTEGER,
sla_time TIME(6),
end_ts VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
I have used the following logic in the past. But I think its what you are after.
For others I will include my hours and seconds calculations as well