6 Replies Latest reply: Nov 25, 2017 2:39 PM by Marco Wedel

# Time Calculation Help

Hi Folks,

In the script side , I'm using below logic to match time 21:59:59

num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without subtraction of 0.0000001 the time 21:59:59 was not matched

What shall I should minus from below to get 20:45:00????

num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Please help!

Regards,

AS

• ###### Re: Time Calculation Help

Any suggestion???

• ###### Re: Time Calculation Help

What are your input and expected output?

• ###### Re: Time Calculation Help

Hello,

What I'm doing is based on Shift table as interval match is looking in CAQ table and taking distinct count of serial number:

My Night Shift numbers are 100% matching with data source with below logic

IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp

But number are not matching for Day shift.

Shift Table :

Script:

Shift:

LOAD Shift_Id,

Shift_Name,

num(Start_Hour) as Start,

num(End_Hour - Time#('00:00:01'),'0,0000000') as End,

Line_Id,

Facility_Id

FROM

[\$(vDataPath)Shift.qvd]

(qvd);

//exit script;

Shifts:

//Shift_Early_Late:

NoConcatenate

LOAD

*

Resident Shift

where End > Start;

Shift_Night:

NoConcatenate

LOAD

*

Resident Shift

where Start > End;

Shift_Night_1:

Concatenate (Shifts)

LOAD

Shift_Id,

Shift_Name,

Start,

1 as End,

Line_Id,

Facility_Id

Resident Shift_Night;

Shift_Night_2:

Concatenate (Shifts)

LOAD

Shift_Id,

Shift_Name,

0 as Start,

End,

Line_Id,

Facility_Id

Resident Shift_Night;

drop tables Shift, Shift_Night;

CAQ:

LOAD

date(floor(Date.tmp)) as Date,

num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!

year(Date.tmp) as Year,

month(Date.tmp) as Month,

week(Date.tmp) as Week,

Day(Date.tmp) as Day,

num(Date.tmp) as NumDate,

//dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,

//    dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,

//    dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,

*

;

LOAD

*,

hour(Date.tmp2) as Time.tmp,

IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp  // Logic for night shift

;

LOAD

num(Date) as Date.tmp2,

timestamp(Date) as Date_Org,

Facility_Id,

Product,

Variant,

Station,

Serial,

Characteristic,

Values,

Status,

DTTSERF

FROM

[\$(vDataPath)CAQ_table.qvd]

(qvd);

left join (CAQ)

IntervalMatch (Time,Facility_Id)

Load distinct

Start,

End,

Facility_Id

Resident Shifts;

left join(CAQ)

LOAD distinct

Facility_Id,

Start,

End,

Line_Id,

Shift_Name

Resident Shifts;

drop table Shifts;

Thanks,

Amit

• ###### Re: Time Calculation Help

You requirement is not clear.

>>What shall I should minus from below to get 20:45:00????

>> num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Well it depends on the value of Date,tmp. I am not sure what you are trying to do. To get the value of time 20:45, use

=#Time('20:45', 'hh:mm')

=MakeTime(20, 45)

• ###### Re: Time Calculation Help

Hi Amit,

Not quite sure if I'm getting the question right but it seems to me that you can use a FLOOR statement in your script

When you use time or datetime it only displays the number in another format it doesn't drop the decimal values after the seconds.

DATETIME(FLOOR(Date.tmp *24*60*60)/ (24*60*60))

Floor is a function which round the values to the first lower integers, so 5.7 = 5.

A date is an integer, while the hours, minutes and seconds are decimal values. Multiplying with 24 makes the hours integers, when adding a multiply of 60 the minutes are integers etc.

So floor(date.tmp*24*60*60) / (24*60*60) rounds your date to seconds

• ###### Re: Time Calculation Help

Hi,

maybe you could use something like

`Time#(Time(Frac(Date.tmp),'hh:mm:ss'),'hh:mm:ss')`

to avoid Rounding Errors

See also:

convert seconds to hh:mm:ss in script

Reference DateTime for calculating occupancy

hope this helps

regards

Marco