Time Calculation Queries

This query demonstrates different calculations combining HHMM times and Date fields with TimeStamps:

select ticket_date,ticket_time,
ticket_time/100 "Hour",
ticket_time-100*(ticket_time/100) "Minute",
60*(ticket_time/100)+ticket_time-100*(ticket_time/100) "Minutes",
cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440  "Frac day",
cast(ticket_Date as timestamp )+cast ( .01 + (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440  "time day",
return_plant_dt "Return DateTime",
floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) ) "Elapsed Mins",
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60) "Elapsed HH",
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60) ) ) "Elapsed MM",
100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60)  +
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60) ) ) "Elapsed HHMM"
 from artslshd
where ticket_date = :Test_Date and return_plant_dt is not null and ticket_time is not null

Sample Elapsed Time Review

select Plant_no,cust_no,ticket_no,ticket_date,ticket_time,
return_plant_dt "Return DateTime",
floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) ) "Calc Elapsed Mins",
100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60)  +
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60) ) ) "Calc Elapsed HHMM",
Elapsed_time "Sys Elapsed Time"
 from artslshd
where ticket_date = '3/31/08' and return_plant_dt is not null 

UPDATE artslshd
SET ELAPSED_TIME=
  100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60)  +
  floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60 -
  floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100*(ticket_time/100))  as numeric (11,6) ) /1440) )/60) ) )  
where ticket_date BETWEEN :Beg_Date and :End_Date and return_plant_dt is not null  and ticket_time is not null
 and plant_no in ('P1', 'P2', 'P3');

Older Example

Example Time Query

select artdriv.driver_name,plant_no,truck_no, ticket_date,ticket_no,artslsdt.qty_sold, ticket_time,

 floor(1440*(batch_end_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440) ) "TB Time",
floor(.5 + 1440*(leave_plant_dt-batch_end_dt))  "End Batch to Leave",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt))  "To Job Minutes",

FLOOR(  floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job Hrs",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt))- 60*FLOOR(  floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job Min",

100*FLOOR(  floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60)  +
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt))- 60*FLOOR(  floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job HH:MM",


floor(.5 + 1440*(begin_pour_dt-arrive_job_dt))  "Waiting",
floor(.5 + 1440*(end_pour_dt-begin_pour_dt))  "Pour",
floor(.5 + 1440*(leave_job_dt-end_pour_dt))  "Washing",
floor(.5 + 1440*(return_plant_dt-leave_job_dt))  "To Plant",

 floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440 ) ) "TTR_MIN",

100*floor(
 floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440 ) ) / 60 ) +

 floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440 ) ) -
60*floor(
 floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440 ) ) / 60 )  TTHHMM


from artslshd 

inner join artslsdt td on artslsdt.session_no=artslshd.session_no and artslsdt.trans_no=artslshd.trans_no and artslsdt.line_no=1

inner join artdriv on artdriv.driver_code = artslshd.driver
where driver is not null and batch_start_dt is not null and batch_end_dt is not null
and ticket_date between:Begin_date and :End_date and driver is not null  and void_flag ^='Y'
order by artdriv.driver_name, ticket_date, ticket_no