Date calculations

[JoseGarcia]

Hi there, 

need to substract date & time dimension to give me total time taken.
Dimension data is displayed: 

formula is:
axle1finish - axle1start

Using date & time function, but unable to get data in a way that allows me to perform any calculation.
Could anyone assist?
Thanks

[mato32188]

Hi @JoseGarcia ,

one of possible option is to play a bit with a string functions like:

load

(date#(left(right(start,len(start)-4),20),'DD MMM YYYY hh:mm:ss')) as start,
(date#(left(right(end,len(end)-4),20),'DD MMM YYYY hh:mm:ss')) as end

from xxx;

Then you can simply use expression sum(end-start) with result in days.

BR

m

[stevejoyce]

interval(axle1finish - axle1start, 's') will show you difference in seconds

interval(axle1finish - axle1start, 'm:s') will show you difference in minutes:seconds

[JoseGarcia]

hi Mato32188, thanks for your suggestion. 

value is returning false.

[JoseGarcia]

Steve, 
Thanks for your suggestion, value is returning false.

[stevejoyce]

Can you confirm your 2 fields are actually timestamp fields. Otherwise you will first need to use timestamp#(field, 'timestamp format of your text') to convert field type.

[JoseGarcia]

Steve, 

my field data is displaying in this way:

Wed Oct 06 2021 14:27:09 GMT+0100 (BST)

Any ideas how could set the timestamp?

[mato32188]

Hi @JoseGarcia ,

load

(date#(left(right(axle1start,len(axle1start)-4),20),'MMM DD YYYY hh:mm:ss')) as axle1start,
(date#(left(right(axle1finish ,len(axle1finish )-4),20),'MMM DD YYYY hh:mm:ss')) as axle1finish 

FROM xxx;

BR

m

[stevejoyce · Accepted solution]

@mato32188 you have day before month so it's not matching syntax.

@JoseGarcia first convert field to be recognized as timestamp and then you can use interval.

convert to timestamp:

(timestamp#(left(right(axle1start,len(axle1start)-4),20),'MMM dd YYYY hh:mm:ss')) as axle1start,
(timestamp#(left(right(axle1finish ,len(axle1finish )-4),20),'MMM dd YYYY hh:mm:ss')) as axle1finish 

duration using interval:

interval(axle1finish - axle1start, 's') will show you difference in seconds

interval(axle1finish - axle1start, 'm:s') will show you difference in minutes:seconds

[mato32188]

Yes. You are right.

@JoseGarcia ,

load

(date#(left(right(axle1start,len(axle1start)-4),20),'MMM DD YYYY hh:mm:ss')) as axle1start,
(date#(left(right(axle1finish ,len(axle1finish )-4),20),'MMM DD YYYY hh:mm:ss')) as axle1finish 

FROM xxx;

BR

m