This post concerns FME 2017.1 and is a follow up to my previous article on dates and datetimes in FME 2017.0
FME 2017: Dates, DateTimes, and their use in Temporal Calculations
If you haven’t read that article, it would be a good starting point before reading this one. Go ahead. It’ll just take a little time…
FME 2017.0, in addition to the existing TimeStamper and DateFormatter transformers, gained a new transformer – the DateTimeCalculator – for carrying out temporal calculations. There were also new functions for datetimes built right into the text editor dialog.
The DateTimeCalculator and related functions are special, not just for their functionality, but because they involved a whole new core method of defining and storing dates, times, and datetimes in FME. This method includes support for fractional seconds, time zones, leap seconds, and a bunch of other cool stuff; but it wasn’t used by the existing TimeStamper and DateFormatter transformers.
Well, in FME 2017.1 the TimeStamper and DateFormatter transformers have been deprecated, and replaced with two new transformers: the DateTimeStamper and the DateTimeConverter, and both of these do use the new core datetime definitions.
Let’s see what this means for you; but first remember that:
- Datetime means a date and time in one value, for example: 20170711152410.8007097
- Date means a date value only, for example: 20170711
- Time means a time value only, for example: 152410.8007097
Oh and notice that times and datetimes can be followed by UTC offsets.
- Datetime with UTC offset: 20170711152410.8007097-05:00
- Time with UTC offset: 152410.8007097-05:00
Creating Datetime Values with the DateTimeStamper
In truth, the old TimeStamper was misnamed because it too worked with dates, not just times, as its parameters dialog shows:
Its simple dialog gave a choice of datetime formats and an attribute to write it to, as does the DateTimeStamper in 2017.1:
The Type parameter lets you choose from creating a Datetime value, Date, or just Time.
The Timezone parameter allows you to choose either the UTC or your system’s Local time zone (remember, a virtual machine such as used by FME Cloud might reside in a different time zone to where you are physically located), and the UTC Offset parameter specifies whether you want that time zone appended to the result:
|Time Zone: Local||Time Zone: UTC|
|UTC Offset: Yes||152759.0497648-05:00||202759.0497648+00:00|
|UTC Offset: No||152759.0497648||202759.0497648|
UTC offset is an important choice to make if you plan to use the result in a DateTimeCalculator. For example,
- 20170731 – 20170711113000 = P19DT12H30M
- 20170731 – 20170711113000-07:00 = Null
i.e. we’ll automatically handle subtracting one datetime from another, if both have a UTC offset defined, or when neither have a UTC offset defined (because we can infer both are the same). But when only one datetime in a subtraction has a UTC offset, then the result is null. That’s because in general we can’t tell what time zone a missing offset implies.
In short, don’t add UTC offset to a timestamp if you want to subtract it from a datetime without one.
The rest of the DateTimeStamper is fairly obvious. The only thing you might wonder is why the DateTimeStamper has a fixed output format, when the old TimeStamper allowed you to customize the result. That’s because we’re adopting a single datetime format in FME. If you want your timestamp in a different format you can change it using the new DateTimeConverter…
Converting Datetime Values with the DateTimeConverter
The DateTimeConverter transformer replaces the now-deprecated DateFormatter. Again its name is more apt because it handles dates, times, and datetimes.
It’s parameters look like this:
Here the timestamp I created with the DateTimeStamper is converted to ISO datetime format. Notice the power to select the output format from a predefined list, or manually customize it:
As you can see, the list has many options, including ISO standards, various FME formats, and Exif format. The preview window shows the result of the conversion, and even includes a field for you to enter test values:
Also notice the Quick Reference guide at the foot of the dialog, that explains all of the different format flags such as %d, %m, and %Ez.
One specific metacharacter that is of interest is the $ (dollar) character. Like in a regular expression, this denotes the end of the string. You can use it to enforce strict matching. For example, take “21170102”. If you convert it to a time (%H%M%S) the result will be 211701 (9:17:01 pm). However, include the $ at the end of the time definition (%H%M%S$) and the result will be a null. In the first case a time may be extracted, even though there are extra digits. In the second case there is no match because the format string dictated that the input contains a time value and no trailing characters.
It’s perhaps not clear, but the Input and Output Format parameters support conditional values. The most useful example I could think of is to use it with a Generic writer to vary the datetime string depending on the output format; for example:
So if the user chooses to write to GML, FME creates a date in ISO format; if they choose to write JPEG, it creates a date in Exif format.
But any conversion – conditional or not – only works when the source dates are valid. What happens if you doubt your input data? The one parameter I haven’t mentioned so far is Repair Overflow…
Validation & Repair
The Repair Overflow option is a way to have FME fix bad values in the data; where the date matches the input format, but has one or more components that overflow the permitted ranges. For example, the date 20040452 overflows in the day component and – if chosen – FME repairs this to 20040522:
The date 20161332 overflows in both the day and month. When Repair Overflow is set to yes, this date is repaired to 20170201 (1st February). That’s because the month overflow gets corrected to January 2017, but then the day overflow pushes it over to February. The number of days in the month is counted, so 20161432 is repaired to 20170304 (because there are fewer days in February).
Such an overflow sounds unlikely, but it might be more common than you think thanks to leap seconds. Leap Seconds are adjustments made to UTC, adding a second at certain times. I believe December 31st 2016 had a leap second inserted as 23:59:60! If you had a datetime with that value, FME repairs it to 20170101000000 so that the datetime is valid for other uses.
Hopefully your data won’t have such overflows (FME’s new functionality won’t generate them for sure) but if it does, this tool can help fix it.
If you don’t want FME to try a repair, and the source data contains a bad date, then it will be output through the transformer’s <Rejected> port. This is a great way to validate your data without changing it. For example, set both the input and output format to FME Date to reject bad data, but pass good data untouched. It’s also a way you can reject datetime values containing leap seconds (for example), to attempt further processing on these values.
Functions for Handling Datetime Time Zones
Besides the two new transformers there are other new additions to 2017.1 in the form of a set of new functions for handling time zones:
@TimeZoneSet is – obviously – for setting the time zone on a datetime or time attribute; but it can also be used to change a datetime from one time zone to another, including the local time zone (local to the operating system) which respects daylight savings.
@TimeZoneRemove – just as obviously – removes the time zone information from a datetime/time attribute, and @TimeZoneGet retrieves the time zone information. This might be useful where you have a datetime and want to know what time zone it is in. Rather than having to think about what regular expression would pull out a time zone (it’s this lovely string: ([+-](?:2[0-3]|[0-9]):[0-5][0-9])$) you can just use this new FME function.
I hope you have some data with dates in it, because these new tools are so much fun to use. I love being able to play with them. And there is more to come. I believe we plan to add fractional second and UTC offset support for all formats that support such a thing.
PS: Every member of staff got a personal slide at the recent FME User Conference. Here’s mine. I must have been in sarcastic mode that day, and the DateTimeCalculator clearly helped:
Mark IrelandMark, aka iMark, is the FME Evangelist (est. 2004) and has a passion for FME Training. He likes being able to help people understand and use technology in new and interesting ways. One of his other passions is football (aka. Soccer). He likes both technology and soccer so much that he wrote an article about the two together! Who would’ve thought? (Answer: iMark)