Skip to main content
Solved

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

  • November 27, 2023
  • 9 replies
  • 62 views
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

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?

    This post is closed to further activity.
    It may be an old question, an answered question, an implemented idea, or a notification-only post.
    Please check post dates before relying on any information in a question or answer.
    For follow-up or related questions, please post a new question or idea.
    If there is a genuine update to be made, please contact us and request that the post is reopened.

    9 replies

    nielsgerrits
    VIP
    Forum|alt.badge.img+61

    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).

     

     

    Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
    virtualcitymatt
    Celebrity
    Forum|alt.badge.img+47

    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+47

    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
    Forum|alt.badge.img+61

    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.

    Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
    geomancer
    Evangelist
    Forum|alt.badge.img+59
    • geomancerEvangelist
    • Evangelist
    • November 27, 2023

    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

    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
    Forum|alt.badge.img+61
    • nielsgerritsVIP
    • Best Answer
    • November 27, 2023

    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?

    Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
    S

    Oh, AttributeSplitter is even better than the sub StringExtractor

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

    geomancer
    Evangelist
    Forum|alt.badge.img+59
    • geomancerEvangelist
    • Evangelist
    • November 28, 2023

    Oh, AttributeSplitter is even better than the sub StringExtractor

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

    image

    Community Stats

    32,120
    Posts
    121,954
    Replies
    39,772
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement
    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.