INTERVAL usage difficulties when creating date

[#1]

Dear all,

1) I am calculating with INTERVAL the working hours as follows (in the LOAD statement):

CODE: interval(MaxTimeStamp-MinTimeStamp) as WHOURS

Note: worked as a charm

When adding WHOURS to a chart with formatting as "Interval" its is adding up all hours above 24 without a problem.

2) Then I put one more condition - having working time starting at 08:00AM I don't wanna count the time before 08:00AM as working:

Few of the codes I tried:

a) interval(MaxTimeStamp-time('08:00','hh:mm') as WHOURS

b) interval(MaxTimeStamp-maketime(8)) as WHOURS

c) interval#(interval(time(MaxTimeStamp,'hh:mm')'-time('08:00','hh:mm')),'hh:mm') as WHOURS

PROBLEM: When adding the field WHOURS to a chart with formatting "Interval" it is showing long sequence of numbers, something like 9905220:2023. Whenr I use the chart formattting "Time - hh:mm" it is showing properly the hours per raw, but of course the totals are wrong since they can't show more than 24hours.

What could be the reason for the formatting issue in the chart?

Thank you in advance.

KY

[Anonymous · Accepted solution]

Kalin,

I guess that the MaxTimeStamp field contains date and time.  Try use frac() function to cut only the time part, removing days:

frac(MaxTimeStamp)

and use it in your interval calculations.

Regards,

Michael

[#3]

Well, dear Michael, your guess is totally correct.

In fact I had the same guess with the significant difference that I did NOT have a clue whatsoever how to strip the date from the Time-stamp.

Thank you very much for the quick and accurate response.

Regards,

Kalin