Solved

Directory and File Pathnames - wrong timezone

  • 26 March 2017
  • 6 replies
  • 2 views
sigtill
Rank
Badge +21

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?

icon

Best answer by sigtill 27 March 2017, 12:15

View original
The real SigTill

6 replies

sigtill
Rank
Badge +21

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
Rank
Badge +21

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
Badge

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
Badge

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
Rank
Userlevel 4
Badge +13
@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
Rank
Badge +21
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

28,731
Posts
109,530
Replies
36,567
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.