Skip to main content
Solved

Total Seconds hh:mm:ss for fields over 24 hours

S

Hello – I am hoping someone can help.

 

I asked a similar question previously and got a working solution:

 

https://community.safe.com/s/question/0D5Dm00001GWvsMKAT/calculating-total-second-from-hhmmss-field

 

However that solutions only works for hh:mm:ss fields that are under 24 hours as soon as that field is over the 24 hours I get a blank value.

 

@DateTimeDiff(@Value(Duration),000000,seconds)

 

Does anyone know of an alternative solution so it would calculate the total seconds for records under and above 24 hours?

 

Many,

Thanks

Best answer by nielsgerrits

virtualcitymatt wrote:

Oh, AttributeSplitter is even better than the sub StringExtractor

When doing calculations, you need to add @Evaluate(), else it will be handled as a string expression.

 

So you need to do...

@Evaluate(
@Value(Time{0})*60*60+
@Value(Time{1})*60+
@Value(Time{2})
)

...when using an AttributeSplitter or...

@Evaluate(
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,1)*60*60+
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,2)*60+
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,3)
)

...if you do it using the regex sample from @geomancer​ or...

@Evaluate(
@Substring(@Value(Time),0,2)*60*60+
@Substring(@Value(Time),3,2)*60+
@Substring(@Value(Time),6,2)
)

...when using the substring function as @virtualcitymatt​ suggests. 

 

I think using a substring might the best answer as you can do it in one go and substring is probably faster / less complex than regex?

View original
    Did this help you find an answer to your question?

    9 replies

    nielsgerrits
    VIP

    So your data looks like...

    25:62:62

    ...and you need...

    93782

    ...seconds? Then split the string with an AttributeSplitter based on : and use an AttributeCreator to do (hours x 60 x 60) + (minutes x 60) + (seconds).

     

     

    virtualcitymatt
    Celebrity
    Forum|alt.badge.img+34

    Ohhh thats an interesting one - I'm not 100% sure if this is supported or not by FME's built in DateTime tools.

     

    perhaps @nielsgerrits​ has a clue if it is.

     

    Another option, all be it much less ideal is to extract the various parts of the string and use an ExpressionEvaluator to calculate the number of seconds.

     

    You can use the SubstringExtractor to create new attribute _hours, _mins_secs

     

    and then the ExpresisonEvaluator to do the math

     

    image 

     

    virtualcitymatt
    Celebrity
    Forum|alt.badge.img+34
    nielsgerrits wrote:

    So your data looks like...

    25:62:62

    ...and you need...

    93782

    ...seconds? Then split the string with an AttributeSplitter based on : and use an AttributeCreator to do (hours x 60 x 60) + (minutes x 60) + (seconds).

     

     

    Oh, AttributeSplitter is even better than the sub StringExtractor

    nielsgerrits
    VIP
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    I think it is doable in one AttributeCreator using regex to find the 3 inputs from the string, but I could not find the time to write it out.

    geomancer
    Evangelist
    Forum|alt.badge.img+46
    • geomancerEvangelist
    • Evangelist
    • November 27, 2023
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    Sure 😀 

    Catching matches is probably easiest:

    @SubstringRegularExpression(@Value(Input),(\d+):(\d+):(\d+),0,1)*60*60+
@SubstringRegularExpression(@Value(Input),(\d+):(\d+):(\d+),0,2)*60+
@SubstringRegularExpression(@Value(Input),(\d+):(\d+):(\d+),0,3)

     

    S
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    Hey @geomancer​ when I try this I get this result: image@nielsgerrits​ I get also this result when splitting the field into hours minutes and second and using this formula

     

    (@Value(QueueWaitH) * 60 *60) + (@Value(QueueWaitM) * 60) +@Value(QueueWaitS)

     

    any ideas why?

     

    thanks for all the help!

     

     

     

    nielsgerrits
    VIP
    • nielsgerritsVIP
    • Best Answer
    • November 27, 2023
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    When doing calculations, you need to add @Evaluate(), else it will be handled as a string expression.

     

    So you need to do...

    @Evaluate(
@Value(Time{0})*60*60+
@Value(Time{1})*60+
@Value(Time{2})
)

    ...when using an AttributeSplitter or...

    @Evaluate(
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,1)*60*60+
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,2)*60+
@SubstringRegularExpression(@Value(Time),(\d+):(\d+):(\d+),0,3)
)

    ...if you do it using the regex sample from @geomancer​ or...

    @Evaluate(
@Substring(@Value(Time),0,2)*60*60+
@Substring(@Value(Time),3,2)*60+
@Substring(@Value(Time),6,2)
)

    ...when using the substring function as @virtualcitymatt​ suggests. 

     

    I think using a substring might the best answer as you can do it in one go and substring is probably faster / less complex than regex?

    S
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    @nielsgerrits​  that worked perfectly - thank you very much :)

    geomancer
    Evangelist
    Forum|alt.badge.img+46
    • geomancerEvangelist
    • Evangelist
    • November 28, 2023
    virtualcitymatt wrote:

    Oh, AttributeSplitter is even better than the sub StringExtractor

    FME will automatically use @Evaluate when you use the Arithmetic Editor.

    image

    Reply


    Helpful Members This Week

    Community Stats

    30,890
    Posts
    117,078
    Replies
    38,698
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.