Calculate difference in hours between two strings

The posted link to the screenshot is broken, but generally speaking this is the way to do it:

1. Convert both fields to the FME datetime format using the DateTimeConverter transformer
2. Use the DateTimeCalculator (Mode = Calculate interval) to calculate the difference

Looks like the data input into the DateTimeConverter are being rejected. Unsure why. Probably because it's a string.

Hi @mariofederis,

Another way to do this is inside the AttributeManger using Date/Time Functions. Set the value of the Duration attribute to the following expression:

@DateTimeDiff(@Value(TimeEndedCTZone),@Value(TimeStartedCTZone),hours)

Next, you will have to reformat your date string attributes (TimeEndedCTZone and TimeStartedCTZone) using a DateTimeConverter, you will want to use the following output format:

%d/%m/%y %H:%M %p

Hope this helps.

- Chris

Difference_in_Hours.fmwt

The posted link to the screenshot is broken, but generally speaking this is the way to do it:

1. Convert both fields to the FME datetime format using the DateTimeConverter transformer
2. Use the DateTimeCalculator (Mode = Calculate interval) to calculate the difference

Chris, I tried the attribute manager with said expression previously but received the following mesage: 'AttributeManager: Failed to parse '01/11/18 5:00 AM' as a datetime value. Not sure what I'm missing.

It also appears the input in your example is a datetime data type instead of string

Hi @mariofederis,

Another way to do this is inside the AttributeManger using Date/Time Functions. Set the value of the Duration attribute to the following expression:

@DateTimeDiff(@Value(TimeEndedCTZone),@Value(TimeStartedCTZone),hours)

Next, you will have to reformat your date string attributes (TimeEndedCTZone and TimeStartedCTZone) using a DateTimeConverter, you will want to use the following output format:

%d/%m/%y %H:%M %p

Hope this helps.

- Chris

Difference_in_Hours.fmwt

I tried specifying the input format but no luck. Still being rejected. 

Two reasons:

• %Y refers to a 4-digit year, yours seems to have only two digits
• %H refers to a two-digit 24 hour clock, your timestamp seems to use 1 or 2 digits and AM/PM notation

Try the following in the DateTimeConverter:

%d/%m/%Y %l:%M %p

Which will let us discover a weakness in the DateTimeConverter, since it doesn't seem to handle two-digit years in the input mask, for some weird reason.

I fixed this by using a StringReplacer first to add "20" to the year:

But since you're creating the timestamps yourself in the AttributeManager, you'll probably be much better off by "fixing" the timestamp there.

Perhaps I should have explained further. TimeStartedCTZone and TimeEndedCTZone are actually created fields within an Attribute Manager (see below).

They are created from the following expression in a text editor: @DateTimeFormat(@Value(dateStarted),%m/%d/%y) @DateTimeFormat(@Format(%04d00,@Value(timeStarted)),%l:%M %p)

Unfortunately, these two attributes are not in the feature reader, but instead, created in the translation. Hence, I cannot change their types. Or I'm unsure how. 

@mariofederis - try using the StringSearcher to extract the time from the string and save that to a new attribute. Then, convert the time to the FME output format before passing it into the DateTimeCalculator. You will need to set the Input format to

%H:%M

Then, you can calculate the time difference (Duration) using the DateTimeCaluclator with the StartTime and EndTime attributes that were created in the StringSearcher.

Difference_in_Hours_String_Search.fmwt

David, I went ahead and changed the format of TimeStartedCTZone and TimeEndedCTZone to include a 4 digit year using %Y.

But when I add the recommended DateTimeCalculator, it looks like the inputs are rejected with the following message:

fme_expression_warnings{0}.message (string): Failed to evaluate expression '@DateTimeDiff(01/11/2018 7:22 AM,01/11/2018 5:52 AM,hours)'. Result is set to null

These were my settings:

Two reasons:

• %Y refers to a 4-digit year, yours seems to have only two digits
• %H refers to a two-digit 24 hour clock, your timestamp seems to use 1 or 2 digits and AM/PM notation

Try the following in the DateTimeConverter:

%d/%m/%Y %l:%M %p

Which will let us discover a weakness in the DateTimeConverter, since it doesn't seem to handle two-digit years in the input mask, for some weird reason.

I fixed this by using a StringReplacer first to add "20" to the year:

But since you're creating the timestamps yourself in the AttributeManager, you'll probably be much better off by "fixing" the timestamp there.

I attempted the recommended setup, but features are still being rejected.

I also tried the workspace provided with the same result. I received the following message: 'fme_expression_warnings{0}.message (string): Failed to evaluate expression '@DateTimeDiff(7:22,5:52,HOURS)'. Result is set to null'

Hi @mariofederis

DateTimeCalculator accepts datetime values in FME format only. I.e., before you attempt to calculate intervals, you need to alter your values format using DateTimeConverter.

Hi @david_r

We made decision not to support two digit year for parsing... at least for now. There are several different standards (I like the Excel approach: https://support.microsoft.com/en-ca/help/214391/how-excel-works-with-two-digit-year-numbers - it is versatile and confusing at the same time). As FME is used by users familiar with different formats/tools/standards, we wouldn't be able to find an option good for all. To avoid confusion and data misinterpretation, we delegated setting up the two year interpretation rules to those who know their data best.

This might change in the future. We might be able to implement some kind of WB setting or workspace parameter that will control two digit year interpretation or introduce a cutoff year. We will rely on users' input/feedback while making this decision.

David, I went ahead and changed the format of TimeStartedCTZone and TimeEndedCTZone to include a 4 digit year using %Y.

But when I add the recommended DateTimeCalculator, it looks like the inputs are rejected with the following message:

fme_expression_warnings{0}.message (string): Failed to evaluate expression '@DateTimeDiff(01/11/2018 7:22 AM,01/11/2018 5:52 AM,hours)'. Result is set to null

These were my settings: