Skip to main content
  • EN

Hi, I have a workspace that i want to compare timestamps between two rows in a database based on some matching information.

i’m using an sql extractor to pull the pertinent information, which spits out something like this:
 

IMAP TIME                                    SUBJECT                   DATE
Tue Aug 20 02:42:25 EDT 2024    XXXXXXX_#6337.4    8/20/2024
Tue Aug 20 02:42:52 EDT 2024    XXXXXXX_#6337.4    8/20/2024
Tue Aug 20 04:06:13 EDT 2024    XXXXXXX_#704.0      8/20/2024
Tue Aug 20 04:06:41 EDT 2024    XXXXXXX_#704.0      8/20/2024
Tue Aug 20 05:33:15 EDT 2024    XXXXXXX_#7274.2    8/20/2024
Tue Aug 20 05:33:47 EDT 2024    XXXXXXX_#7274.2    8/20/2024

I’m then using a StringSearcher to extract the time with the regex \d+:\d+:\d+ and this gives me the time in essentially a %H:%M:%S format:


Extracted   original IMAP (for comparison)
02:42:25    Tue Aug 20 02:42:25 EDT 2024
02:42:52    Tue Aug 20 02:42:52 EDT 2024
04:06:13    Tue Aug 20 04:06:13 EDT 2024
04:06:41    Tue Aug 20 04:06:41 EDT 2024
05:33:15    Tue Aug 20 05:33:15 EDT 2024
05:33:47    Tue Aug 20 05:33:47 EDT 2024
06:22:19    Tue Aug 20 06:22:19 EDT 2024
06:22:47    Tue Aug 20 06:22:47 EDT 2024
06:32:26    Tue Aug 20 06:32:26 EDT 2024
06:32:58    Tue Aug 20 06:32:58 EDT 2024

The extracted result isn’t recognized as time format, so, i’m trying to feed it into DateTimeConverter with the source format: %H:%M:%S (also tried without %S and as %S$) the transformer fails and i’m not sure why.

i get the following output in the translation log:


Restoring 10 feature(s) from FME feature store file `\AppData\Local\Temp\wb-cache-CIP_EMAIL_HEARTBEAT_8-20-24-KdQmlH\Main_StringSearcher_5 -1 61 fo 0 MATCHED  0  c6df3019477d52d3e74dc521846172057a12859f.ffs'
The below feature caused the translation to be terminated
Storing feature(s) to FME feature store file `\Documents\FME\Workspaces\CIP_EMAIL_HEARTBEAT_8-20-24_Edit_log.ffs'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Feature Type: `FEATURE_TYPE'
Attribute(string: UTF-8)           : `02:42:25' is <null>
Attribute(string: UTF-8)           : `BMS_Source' has value `Siemens'
Attribute(string: UTF-8)           : `Date' has value `08-20-2024'
Attribute(string: UTF-8)           : `Email_Source' has value `FME_Outbound'
Attribute(string: UTF-8)           : `IMAP_Time' has value `Tue Aug 20 02:42:25 EDT 2024'
Attribute(string: UTF-8)           : `PARAMETER_EMAIL_BODY' has value ` 

DateTimeConverter_2_<Rejected> (TeeFactory): DateTimeConverter_2_<Rejected>: Termination Message: 'DateTimeConverter_2 output a <Rejected> feature.  To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''
An error has occurred. Check the logfile above for details
 

now, my assumption is that top attribute is the error Attribute(string: UTF-8)           : `02:42:25' is <null>

but i don’t know why it is showing up as “is <null>”

i don’t see any extra characters or anything in it - the output from the previous step (i have formatting turned on)

 

the attribute says it is UTF-8 string:
 

 

I feel like i’m missing something obvious, but, i don’t know what - any advice would be appreciated

Hi @parke372 For some reason the log is showing that you have an attribute called `02:42:25' with a value of <null>. I’m unable to reproduce this problem myself with FME 2022.2 What is the format of the timestamp that you want to convert to? Can you try converting the full date/time stamp to the desired format?

Hi @parke372 For some reason the log is showing that you have an attribute called `02:42:25' with a value of <null>. I’m unable to reproduce this problem myself with FME 2022.2 What is the format of the timestamp that you want to convert to? Can you try converting the full date/time stamp to the desired format?

Dan,

thanks for the response! the output of the string searcher shows the attribute as _first_match and should be a value of 02:42:25. 

 


so when it is getting passed into the DateTimeConverter it isn’t picking up the _first_match attribute and instead just sees the value as the attribute name, with a null value? Weird.

Odd thing is, i removed the string searcher, added it back configured the same, and, now it feeds in fine. go figure. i’ve been stuck on the idea that it was not recognizing the input format, not realizing the actual issue, so, i appreciate that a lot! 

Now that it is converting it, i think i’m all set, thanks!

 





 

Whilst not part of the question, noting there is a potential optimisation here as well by just using the raw IMAP Time inside DateTimeConverter, and not needing to extract out time separately using RegEx.

Usually any custom date/time pattern can be parsed by DateTimeConverter by using appropriate Date/Time Formatters.

https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/!Transformer_Parameters/Date-Time-Functions.htm#Format

For the sample data, it appears it is in format

* %b %d %H:%M:%S EDT %Y (although use an %e instead of %d if the raw input data does not have leading zeroes for the Date)

Outputs an FME Date/Time string straight from the source data.

 

Bwn,

Thanks for the observation. I was originally trying to do that, but, running into a similar issue where it was failing, leading me to suspect that it was struggling with the EDT, but now i think that there may have been some kind of other, likely user induced issue instead - so i’ll definitely look into that. 

Ultimately, I’m looking for response times longer than 5 minutes, so, time along may still make sense but i can try it both ways and see if there is a clear advantage one way or another.

 

Thanks!

Reply


Community Stats

30,163
Posts
114,500
Replies
37,813
Members
Terms & ConditionsCookie 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.