I found this regarding MS SQL timezones, not sure if FME uses this syntax? https://sqlperformance.com/2016/07/sql-plan/at-time-zone
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.
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.
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.
@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
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) !
