我有一种情况来计算分时间戳的差异。 有2桌一个共同的领域,即dw_job_id并想加入2个表和猜测,计算
(scratch.COGIPF_RUNREPORT_test2.end_ts - concat(proct_dt, scratch.dw_job_sla_dim_test.sla_time)
例如,在减法分钟:
(2018-03-05 01:53:14.201000 - 2018-03-05 08:00:00.000000)= -366
它将使用下面的记录是相当清楚的:
第一次查询有end_ts:
sel * from scratch.COGIPF_RUNREPORT_test2 where dw_job_id=1226
结果:
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
第二个查询:
select * from scratch.dw_job_sla_dim_test where dw_job_id=1226
结果:
dw_job_id sla_hour sla_minute sla_time
1,226 8 0 08:00:00.000000
最终的结果应该是:
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
例:
减法分钟:-( 2018年3月5日01:53:14.201000 - 2018年3月5日08:00:00.000000)= -366
数据类型:
sla_hour INTEGER,
sla_minute INTEGER,
sla_time TIME(6),
end_ts VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,