Skip to main content
Solved

Directory and File Pathnames - wrong timezone

sigtill
Contributor
Forum|alt.badge.img+24

We use a Directory and File Pathnames to extract filenames and path_created_date and store the value for all new files in a database.

When we move to summer-time and winter time, it seems that FME gets 1 hour different and adds all files again. So I have checked the following, to see if the timezone settings were off:

FME Server: own custom server - Western europe standard time - UTC+1

 

Database: MS SQL - Not sure

 

File server: - Western europe standard time - UTC+1

This is really annoying - and it would be great with a universal solution. Any tips or someone had a similar issue?

Best answer by sigtill

Workaround untill better solution. Create features with time -1 and +1 for each, and then assume noone will create the same file the same second on hour before or after.

View original
The real SigTill
Did this help you find an answer to your question?

6 replies

sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • March 26, 2017

I found this regarding MS SQL timezones, not sure if FME uses this syntax? https://sqlperformance.com/2016/07/sql-plan/at-time-zone

The real SigTill
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • Best Answer
  • March 27, 2017

Workaround untill better solution. Create features with time -1 and +1 for each, and then assume noone will create the same file the same second on hour before or after.

The real SigTill
L
Forum|alt.badge.img

Hi @sigtill

I don't think FME introduces the 1 hour error but at the moment I struggle to explain it. Let's try to find the root of the problem.

Directory and File Pathnames Reader reads path/file properties. The file Created/Modified/Accessed properties are captured when the file is created/modified/accessed. Let's focus on Created only for now. The value of the Created property of the file is system time stamp the moment the file is created. On my Windows machine, this time stamp is displayed without time offset/time zone info. I guess, this is what causes the problem.

For example:

  • my system time zone is America/Vancouver which is UTC-07:00 at the moment;
  • two weeks ago, on March 12th, we switched to daylight saving time, i.e. before 2am on March 12th my system time offset was UTC-08:00;
  • if a file was created on March 12th at 1am its Created property will be set to 20170312010000;
  • if a process is ran on March 12th at 4am, the above file will appear to be 3 hours old - when it actually is only 2 hours old.

Do you work with FME 2017? In FME, you can use @DateTimeDiff() function or DateTimeCalculator transformer to find the time interval between now and a file Created value (i.e. to find how old the file is). However, to get the correct results, you will need to tag your file Created values with correct time offset, taking daylight saving time into account.

 

Another option would be to have your file server in UTC (with no daylight saving time ever). FME 2017 @DateTimeNow(utc) function returns current time in UTC which will be easy to compare to files Created values if they are in UTC as well. In other words, if you have your file server in UTC and FME Server runs in any time zone, the problem will be easy to solve with FME 2017 functionality.

We are going to implement time zone conversion functionality (similar to what is described in https://sqlperformance.com/2016/07/sql-plan/at-time-zone ). However, I don't think this will help you solve your problem as you deal with unzoned time stamps - the toughest part is how to assign the correct time zone to them.

Please let us know if you need an assistance implementing these solutions or have any questions.

L
Forum|alt.badge.img
lenaatsafe wrote:

Hi @sigtill

I don't think FME introduces the 1 hour error but at the moment I struggle to explain it. Let's try to find the root of the problem.

Directory and File Pathnames Reader reads path/file properties. The file Created/Modified/Accessed properties are captured when the file is created/modified/accessed. Let's focus on Created only for now. The value of the Created property of the file is system time stamp the moment the file is created. On my Windows machine, this time stamp is displayed without time offset/time zone info. I guess, this is what causes the problem.

For example:

  • my system time zone is America/Vancouver which is UTC-07:00 at the moment;
  • two weeks ago, on March 12th, we switched to daylight saving time, i.e. before 2am on March 12th my system time offset was UTC-08:00;
  • if a file was created on March 12th at 1am its Created property will be set to 20170312010000;
  • if a process is ran on March 12th at 4am, the above file will appear to be 3 hours old - when it actually is only 2 hours old.

Do you work with FME 2017? In FME, you can use @DateTimeDiff() function or DateTimeCalculator transformer to find the time interval between now and a file Created value (i.e. to find how old the file is). However, to get the correct results, you will need to tag your file Created values with correct time offset, taking daylight saving time into account.

 

Another option would be to have your file server in UTC (with no daylight saving time ever). FME 2017 @DateTimeNow(utc) function returns current time in UTC which will be easy to compare to files Created values if they are in UTC as well. In other words, if you have your file server in UTC and FME Server runs in any time zone, the problem will be easy to solve with FME 2017 functionality.

We are going to implement time zone conversion functionality (similar to what is described in https://sqlperformance.com/2016/07/sql-plan/at-time-zone ). However, I don't think this will help you solve your problem as you deal with unzoned time stamps - the toughest part is how to assign the correct time zone to them.

Please let us know if you need an assistance implementing these solutions or have any questions.

@DanAtSafe discovered that even though file properties are displayed without time zone information, the values are somehow time zone aware (e.g. if system time zone is changed, the values change accordingly). Therefore I have filed PR #76737 - PATH R: UTC offset in path_modified/created/accessed_date to investigate whether it is possible to add UTC offset to path_modified_date, path_created_date, and path_accessed_date attribute values read with Directory and File Pathnames Reader.
fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • January 29, 2018
lenaatsafe wrote:
@DanAtSafe discovered that even though file properties are displayed without time zone information, the values are somehow time zone aware (e.g. if system time zone is changed, the values change accordingly). Therefore I have filed PR #76737 - PATH R: UTC offset in path_modified/created/accessed_date to investigate whether it is possible to add UTC offset to path_modified_date, path_created_date, and path_accessed_date attribute values read with Directory and File Pathnames Reader.
Hi @sigtill PR76737 is now fixed in the latest FME 2018.0 betas

 

sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 30, 2018
fmelizard wrote:
Hi @sigtill PR76737 is now fixed in the latest FME 2018.0 betas

 

Great, just in time for Summer Time (https://www.timeanddate.com/time/dst/summer-time.html) !

 

 

The real SigTill

Reply


Community Stats

30,846
Posts
116,941
Replies
38,670
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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