Solved

DateFormatter shifts time if Source Date Format setting is "Unknown - Automatic Detection"?

S
Badge

Hi Everybody,

I am using FME 2016.1.3.1

In date time format I used

* Source Date Format = "Unknown - Automatic Detection"

* Destination Date Format = "FME Date/Time (%Y%m%d%H%M%S)"

Then if the input is "2017-09-13T16:30:01" it will output "20170913173001".

Attached is the FME workspace I used for testing. Did I do anything wrong? I don't want to have the hour shifted.

Thank you!

icon

Best answer by fmelizard 14 September 2017, 22:03

View original

14 replies

S
Badge
Add some information: according to my observation on some data, seems the time shift only happen when the daylight saving time apply (say, summer).

 

 

david_r
Rank
Userlevel 4

Generally speaking, my recommandation is to not use the automatic detection if the format is known and stable.

Setting the source format as follows should avoid the problems you're observing:

%Y-%m-%dT%H:%M:%S
S
Badge
Found another fact: if the input is "2017-09-13 16:30:01" or "20170913163001" (without "T" between date and time), then the time will not be shifted. Seems the issue only happen to ISO datetime format (%Y-%m-%dT%H:%M:%S)

 

 

S
Badge

Generally speaking, my recommandation is to not use the automatic detection if the format is known and stable.

Setting the source format as follows should avoid the problems you're observing:

%Y-%m-%dT%H:%M:%S
Hi David, you are right that setting the source format can avoid the issue, the cost of doing that to me is the FME will not adapt to different input formats when I want to use the same transformer to handle multiple fields, and not sure whether the data provider will consistently provide the same format... if there is no other choice I think setting the source format will be a solution.

 

 

ebygomm
Rank
Userlevel 1
Badge +21

There was a bug in the dateformatter in 2016 affecting automatic detection

https://knowledge.safe.com/questions/30468/dateformatter-issue-20161.html

david_r
Rank
Userlevel 4
Hi David, you are right that setting the source format can avoid the issue, the cost of doing that to me is the FME will not adapt to different input formats when I want to use the same transformer to handle multiple fields, and not sure whether the data provider will consistently provide the same format... if there is no other choice I think setting the source format will be a solution.

 

 

For what it's worth, I've not had much luck using the auto detection in the DateFormatter for other than very basic timestamps.

 

The problem is that there are so many ambiguous ways of writing timestamps. Consider "12-01-17": Is that 12. January 2017 (DDMMYY), 1. December 2017 (US) or 17. January 2012 (ISO)?

 

If the DateFormatter makes a wrong assumption, how will you detect it?

 

S
Badge

There was a bug in the dateformatter in 2016 affecting automatic detection

https://knowledge.safe.com/questions/30468/dateformatter-issue-20161.html

@egomm thank you for the information. The previous bug was for detecting Oracle date according to @LenaAtSafe, and was fixed at version 2016.1.1. The issue I encountered is for detecting and handling ISO format, observed at version 2016.3.1. Does it look like a different issue?
S
Badge

I found an old topic thread at 2014 with the symptom of daylight saving time change.

https://knowledge.safe.com/questions/4783/daylight-savings.html

However, the thread didn't indicate a report to Safe support.

S
Badge

I found an old topic thread at 2014 with the symptom of daylight saving time change.

https://knowledge.safe.com/questions/4783/daylight-savings.html

However, the thread didn't indicate a report to Safe support.

@david_r I noticed that you were on this topic, do you know whether there was anything happen to that after?

 

 

fmelizard
Rank
Userlevel 4
Badge +13

There does indeed seem to be some odd behaviour with the DateFormatter in this scenario. Results vary with the system time settings. The difference is seen when either turning on or off the 'Adjust for daylight saving time automatically' switch (Windows 10) as @sui_huang discovered and is mentioned in the linked thread. The DateFormatter has now been deprecated as of FME 2017 and FME 2017.1 and is replaced with the DateTimeConvertor - This transformer appears to work correctly in this situation no matter how the system settings are set.

S
Badge

There does indeed seem to be some odd behaviour with the DateFormatter in this scenario. Results vary with the system time settings. The difference is seen when either turning on or off the 'Adjust for daylight saving time automatically' switch (Windows 10) as @sui_huang discovered and is mentioned in the linked thread. The DateFormatter has now been deprecated as of FME 2017 and FME 2017.1 and is replaced with the DateTimeConvertor - This transformer appears to work correctly in this situation no matter how the system settings are set.

Thank you for your help Matt!

 

david_r
Rank
Userlevel 4
@david_r I noticed that you were on this topic, do you know whether there was anything happen to that after?

 

 

I don't know -- fortunately I was never touched by that particular issue so I haven't kept up to date. Also, I never use the automatic format detection, too risky for my use cases.
S
Badge
For what it's worth, I've not had much luck using the auto detection in the DateFormatter for other than very basic timestamps.

 

The problem is that there are so many ambiguous ways of writing timestamps. Consider "12-01-17": Is that 12. January 2017 (DDMMYY), 1. December 2017 (US) or 17. January 2012 (ISO)?

 

If the DateFormatter makes a wrong assumption, how will you detect it?

 

I applied a quick solution of specifying the input date format. I am not the first author of the FME script, so I am trying to do every change carefully. I found that some original DateFormatter was used to handle multiple fields together. Some fields are in ISO format, and some fields are in SQL Server format. I think that was why auto-detection was used in the initial authoring. To apply the quick fix I needed to split the transformer into two. The confirmation of the DateFormatter transformer issue convinced me that this change is necessary.

 

Down the road if we are updated to newer FME version I think the new DateTimeConvertor will be preferred.

 

 

david_r
Rank
Userlevel 4
I applied a quick solution of specifying the input date format. I am not the first author of the FME script, so I am trying to do every change carefully. I found that some original DateFormatter was used to handle multiple fields together. Some fields are in ISO format, and some fields are in SQL Server format. I think that was why auto-detection was used in the initial authoring. To apply the quick fix I needed to split the transformer into two. The confirmation of the DateFormatter transformer issue convinced me that this change is necessary.

 

Down the road if we are updated to newer FME version I think the new DateTimeConvertor will be preferred.

 

 

Sounds like a good call, I would probably have done the same thing.

Reply


Community Stats

28,692
Posts
109,433
Replies
36,544
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.