计算Teradata中的时差 [英] Calculate time difference in Teradata

问题描述

我有一个方案来计算以分钟为单位的时间戳差异. 2个表中有一个公共字段，即dw_job_id 并想加入2个表并进行猜测，计算

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:

第一个查询的end_ts:

1st query has 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-03-05 01:53:14.201000-2018-03-05 08:00:00.000000)= -366

Subtraction in minute :-(2018-03-05 01:53:14.201000 - 2018-03-05 08:00:00.000000)= -366

数据类型:

  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.

,(CAST((CAST(end_timestamp AS DATE)- CAST(start_timestamp AS DATE)) AS DECIMAL(18,6)) * 60*24)
  + ((EXTRACT(  HOUR FROM end_timestamp) - EXTRACT(  HOUR FROM start_timestamp))* 60)
  + ((EXTRACT(MINUTE FROM end_timestamp) - EXTRACT(MINUTE FROM start_timestamp))  )
  + ((EXTRACT(SECOND FROM end_timestamp) - EXTRACT(SECOND FROM start_timestamp))/60)
AS "Difference in Minutes"

对于其他人，我还将包括我的小时和秒计算

For others I will include my hours and seconds calculations as well

,(CAST((CAST(end_timestamp AS DATE)- CAST(start_timestamp AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
  + ((EXTRACT(  HOUR FROM end_timestamp) - EXTRACT(  HOUR FROM start_timestamp))* 60*60)
  + ((EXTRACT(MINUTE FROM end_timestamp) - EXTRACT(MINUTE FROM start_timestamp)) * 60)
  + ((EXTRACT(SECOND FROM end_timestamp) - EXTRACT(SECOND FROM start_timestamp)))
AS "Difference in Seconds"  


,(CAST((CAST(end_timestamp AS DATE)- CAST(start_timestamp AS DATE)) AS DECIMAL(18,6)) * 24)
  + ((EXTRACT(  HOUR FROM end_timestamp) - EXTRACT(  HOUR FROM start_timestamp)))
  + ((EXTRACT(MINUTE FROM end_timestamp) - EXTRACT(MINUTE FROM start_timestamp)) / 60.000000)
  + ((EXTRACT(SECOND FROM end_timestamp) - EXTRACT(SECOND FROM start_timestamp)) / 3600.000000)
AS "Difference In Hours"    

这篇关于计算Teradata中的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！