I have two string fields (TimeStartedCTZone, TimeEndedCTZone) formatted as shown below. How can I calculate the difference in Hours despite having strings as inputs instead of datetime data types?
Solved
Calculate difference in hours between two strings
Best answer by lenaatsafe
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.
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.
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.
- 8394 replies
The posted link to the screenshot is broken, but generally speaking this is the way to do it:
- Convert both fields to the FME datetime format using the DateTimeConverter transformer
- Use the DateTimeCalculator (Mode = Calculate interval) to calculate the difference
The posted link to the screenshot is broken, but generally speaking this is the way to do it:
- Convert both fields to the FME datetime format using the DateTimeConverter transformer
- 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.
- 8394 replies
Looks like the data input into the DateTimeConverter are being rejected. Unsure why. Probably because it's a string.
That typically indicates that FME cannot automatically infer the input datetime format and that you have to explicitely specify it in the DateTimeConverter.
+2- Contributor
- 606 replies
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 %pHope this helps.
- Chris
Difference_in_Hours.fmwt
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 %pHope this helps.
- Chris
Difference_in_Hours.fmwt
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
The posted link to the screenshot is broken, but generally speaking this is the way to do it:
- Convert both fields to the FME datetime format using the DateTimeConverter transformer
- Use the DateTimeCalculator (Mode = Calculate interval) to calculate the difference
I tried specifying the input format but no luck. Still being rejected.
+2- Contributor
- 606 replies
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
Have you tried setting the Type to datetime for the TimeStartedCTZone and TimeEndedCTZone in the reader feature type parameters?
You can do this by going to Readers > Update Feature Types or by right-clicking the reader feature type in the Navigator pane.
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 %pHope this helps.
- Chris
Difference_in_Hours.fmwt
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.
- 8394 replies
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 %pWhich 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.
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 %pWhich 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.
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:
+2- Contributor
- 606 replies
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:%MThen, 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
@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:%MThen, 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
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'
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:
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 @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.
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 %pWhich 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.
Hi @david_r
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.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.
+2- Contributor
- 606 replies
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'
If possible, are you able to upload your workspace and sample data? You can use an AttributeRemover to remove any irrelevant/sensitive data and write it out to the same format as the source dataset or as an FFS. The only attributes required are the dates used to create the TimeEndedCTZone and TimeStartedCTZone attributes.
- 7843 replies
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.
@LenaAtSafe is right.
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)
From your description above, the "dateStarted" seems to be formatted in %Y%m%d (standard FME date format), and the "timeStarted" seems to be formatted in %k%M. If so, you can convert them into standard FME date/time format (%Y%m%d%H%M%S) with this expression.
@Value(dateStarted)@Format(%04d00,@Value(timeStarted))- 8394 replies
Hi @david_r
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.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.
Hi Lena, thanks for the explanation!
- 8394 replies
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:
Based on the error message it seems the timestamps weren't properly formatted by the DateTimeConverter first?
Thanks everyone. I ended up converting TimeStartedCTZone and TimeEndedCTZone into FME format within attribute manager before using the Datetimediff function. This eventually produced the correct result (duration)