Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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
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
interval(axle1finish - axle1start, 's') will show you difference in seconds
interval(axle1finish - axle1start, 'm:s') will show you difference in minutes:seconds
hi Mato32188, thanks for your suggestion.
value is returning false.
Steve,
Thanks for your suggestion, value is returning false.
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.
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?
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
@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
Yes. You are right.
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