This article is a guide to dates for FME users.
When I say dates, I don’t mean a date like this:
…even though many of my colleagues have a powerful sushi addiction!
Nor do I mean dates like this:
No, you know that I mean these sort of dates:
So while FME has little help for either your love life or your eating habits (sorry) what it does have in 2017 is the ability to handle more complex calendar dates and to carry out a variety of datetime arithmetic calculations on them.
The DateTimeCalculator Transformer
The most visible addition for FME2017 is a new FME Workbench transformer called the DateTimeCalculator:
This transformer has a number of modes, but the two most useful are:
- Add or Subtract DateTime Intervals
- Calculate Interval between DateTimes
Let’s see what they do…
Adding or Subtracting To/From Dates
The Add or Subtract Interval tool is – of course – the ability to add (or subtract) set periods to an existing date or time.
In this example I have a dataset containing parking offenses. An attribute in that dataset records the date on which an offense took place. I add six months to provide a date by which the fine must be paid:
The date I am writing this is 20170309 (March 9th). If that were the value of DATE, then PaymentDate will be 20170909 (September 9th).
As you can see, there are options to add (or subtract) a number of years, months, days, hours, minutes, and even seconds. That’s a wide enough range of options for almost anyone; excluding only fossil hunters measuring time in eras and epochs!
Calculating Date Intervals
The other chief mode is the Calculate Interval mode. Instead of using one datetime value, it starts off with two values and calculates the interval between them.
For example, here I calculate the interval between two dates held as attributes and return the answer in months:
So if the two dates were 20170309 and 20170909 then the result would be 6 (or -6, depending on which way round the values were). Besides “months”, I can also choose to have the result in a number of other units:
So if I’d chosen to receive the result in days, then it would be 184.
And that’s interesting. Notice that, while 6 months is half of a year, the number of days is not just 365/2. FME is actually returning the proper number of days according to the length of the month. So the difference between 20170609 (9th June) and 20171209 (9th December) is also 6 months, but just 183 days.
You might now be wondering, what about leap years? Yes, FME handles that as well. So FME will report that 20170209 (February 9th) to 20170309 (March 9th) is 28 days, but was 29 last year.
Also note that the transformer may return fractional results. So FME tells me it is just 10.857142857142858 weeks until the upcoming FME Worldwide User Conference!
Hidden Complexity in Dates
You can skip this bit if nerding out about advanced date-handling is not your thing. I’m not offended.
The thing is, there’s a hidden complexity in date calculations, which the leap year example strongly hints at. You must recognize that a certain logic is used:
EndTime – StartTime = Interval iff [if, and only if] StartTime + Interval = EndTime
This logic returns unique answers to ambiguous questions. Best not to think about it too much, and look at some examples!
So, what date is August 30th + 6 months? Answer: February 28th (it doesn’t roll over into March just because February has fewer days).
In the same way, what date is February 29th 2016 + 1 year? Answer: February 28th 2017 (it can’t be February 29th 2017, and it too won’t roll over into March).
And consider, what date is the result of February 28th + 6 months? FME will tell you it is August 28th. However, some folk might want it to be August 31st; because February 28th is the last day of the month and the last day of August is the 31st. In that scenario you’d want “last day of the month” support. It’s actually quite easy (just take the first day of the next month and subtract one), but we also hope to add proper support at a later date.
Basically FME’s results are what I expected; but those are the sort of boundary cases worth thinking about when handling dates.
Strictness
One other thing: there are various forms of datetime (dates, times, datetimes) with or without the option for a UTC offset. This new functionality is set up to be very strict about them. For example, the Calculate Interval function only works when the two dates have the same structure. I don’t just mean the FME standard structure (yyyymmdd as opposed to dd-mm-yyyy) but what temporal type it is.
An FME date is yyyymmdd, but an FME datetime is (excluding fractional seconds) yyyymmddhhmmss. The DateTimeCalculator will not calculate the interval between attributes if one is a date and the other a datetime. The obvious reason is that a fraction of a day in one attribute can’t be accounted for in the other.
We could have made FME automatically remove the time part from the one attribute (but should we round up or down)? Or we could have added a time part to the plain date (but should it be midday, or midnight, or the time of the datetime argument)? And even if both attributes were datetime, if one had a timezone and the other didn’t, what then?
In the end the number of questions – and the possibility that automatically trying to answer them would cause surprising results – made us err on the side of caution and be very strict.
NB: We might revisit that decision though, and reduce the strictness for some cases for 2017.1, so watch this space.
DateTime Functions
While the DateTimeCalculator transformer is the most visible addition, there are parts perhaps less noticeable: Date/Time Functions. These functions power the DateTimeCalculator but also appear in text editor dialogs:
These functions operate in any instance of a text editor, in the same way as string or math functions. Let’s look at a few of them to see what they do.
Obviously DateTimeAdd is the same as (and powers) the Add/Subtract mode in the DateTimeCalculator; similarly DateTimeDiff powers interval calculations.
DateTimeParse is a way to turn arbitrarily formatted datetime values into the “official” FME format. Since DateTimeDiff (for example) only works with dates of the same structure, this is very useful. For example, I can use this to change ISO datetimes (like YYYY-MM-DDThh:mm:ss) into FME datetimes (yyyymmddhhmmss).
The reverse to that is DateTimeFormat, which turns FME datetime values into other (arbitrary) formats, for example when you want to write the value to a writer.
DateTimeNow provides the current date and time. Consider it a direct replacement for the old FME Timestamp function.
Using DateTime Functions
Of course, access to these functions means you can skip a series of DateTimeCalculators (and other transformers), and plant entire complex strings into a single text editor. For example:
@DateTimeDiff(@DateTimeParse(@Value(Date) 2017,%B %d %Y),@DateTimeCast(@DateTimeNow(localUnzoned),date),days)
That’s quite the statement! Basically it is comparing today’s date against a held as an attribute.
- I take the current date using DateTimeNow() as a local time without timezone
- I cast the DateTimeNow() result into a plain date using DateTimeCast()
- I read the date attribute with Value() and then parse it into FME format using DateTimeParse()
- Finally, DateTimeDiff() is used to find the difference between the two newly formatted dates
The result of this is the number of days between the two dates. It avoids using the DateTimeCalculator transformer (and the DateTimeFormatter/DateTimeStamper, which don’t exist yet, but will in 2017.1) but still manages to meet the rules around temporal type needing to match.
Part of that – you might have noticed – is that I hard-coded the year in my parse statement. I don’t really need that component here, but an FME date always has a year and so my attribute date needs a year component to match.
Incidentally, if I do get it wrong, and try to compare two different temporal types, the result will be null.
DateTime Arithmetic Calculation Examples
I have two examples I want to share with you, both of which are the examples used above to explain the functionality. The first (DateTimeCalculator example) was shown in the FME Desktop Deep Dive webinar, and you can download it from the webinar’s archive page.
This workspace reads a set of parking ticket records. It then uses DateTime functionality to check if the ticket is overdue, and by how much.
As you can see, it uses the current DateFormatter transformer, a DateTimeCalculator, and an AttributeManager transformer containing some DateTime functions.
The other example is even smaller:
This workspace reads the list of FME World Tour events directly from the Safe website (with the new HTML Table reader) and compares the date of each event to “today’s” date. It filters the events to show which events are today, which are tomorrow, which are in the future, and which are already completed.
Inspect the workspace and you’ll find everything is built into one TestFilter transformer, using the DateTime function example shown above.
I don’t know if forcing everything into one transformer counts as best practice, but it certainly comes in under the 10 transformer limit!
Testing Dates
The key concept of the FME World Tour example – and it really is very important – is this: do not make comparisons by testing Date1=Date2. All that does is a string-based comparison of two attributes. So, this is wrong:
I even put it in big letters there so you don’t mistakenly do that. It might work in some circumstances, but it is not good practice. Instead what you should do is determine the time interval between two dates and test if it is zero:
OK, here I have squashed the functions inside of the Tester, but you don’t need to do that. You can use the DateTimeCalculator to figure out the difference. Just don’t do a direct comparison of dates. The other advantage of this method is this:
Yes! I can test if Date1 is before Date2 or after. Let’s see you do that so easily with a “Date1 > Date2” string comparison!
More DateTime Goodies
Did I miss anything? Well, quite a lot actually. Here’s a list of what else is changed or new functionality. The first item is a very big deal, according to the number of users who asked for it:
- Fractional Seconds: FME supports fractional seconds, such as a datetime like: 20170315131458.4960507-05:00 (that’s 1:14pm and 58.4960507 seconds)
This is useful for formats that support fractional seconds (such as Oracle and SQL Server) and for timestamps with nanosecond precision.
- Composite Time (Add/Subtract): A composite time is a time difference made up of several elements; like months, weeks, days, and hours. So, although above I added six months to the date of a parking offense, I could (for example) have added “4 months, 3 days, 12 hours”. The DateTimeCalculator transformer has a separate field for each and in functions I simply use ISO8601 duration syntax; for example:
@DateTimeAdd(@DateTimeNow(),-P1Y1MT13H) [Tell me what the datetime was 1 year, 1 month, and 13 hours ago]
- Composite Time (Intervals): Just as I can provide composite times as an argument to a function, so FME can provide interval calculation results as composite times. Again the ISO8601 syntax is used, so FME might tell me that it is P7M27DT6H55M38.0095699S (7 months, 27 days, 6 hours, 55 minutes, and 38.0095699 seconds) until my birthday!
- Timezones and FME Server: I’ve always had problems with timestamps when I’m in one timezone (Central -6:00) but FME is running on a cloud-based server in a another timezone (say Pacific -8:00). Now I don’t have to worry. The DateTimeNow() function lets me specify standard UTC time, so it doesn’t matter where my server is based. I can also ask for local time both with or without timezone.
- FME Hub Transformers: The FME Hub contains an existing set of datetime transformers. These will be deprecated shortly, but will continue to function if used in an existing workspace.
DateTime Goodies to Come
There are still a number of updates we are working on, or planning to do:
- DateTimeFormatter: Look for this new transformer in 2017.1. It’s a replacement for the existing DateFormatter.
- DateTimeStamper: Look for this new transformer in 2017.1. It’s a replacement for the existing TimeStamper.
Read/Write Support: Today there aren’t many formats for which we have proper datetime support. For example, Netezza is the only format that you can write with full UTC timezone support (it has a special data type called timetz). Look for more of these in FME 2018.0.
- Timezone Manipulation: This means support for setting, removing, and even converting timezones. I’m told it will be no later than FME 2018.0
- Timezone Names: Currently the datetime functions allow %z as the UTC timezone. In the future we’ll also have %Z (upper case) to support timezone names.
So there you are. This post finally came to an end. “It’s about time” I hear you say!
If you haven’t tried FME2017 yet, do visit our downloads page and give it a go. Remember you can always get an evaluation license if necessary.
Acknowledgements
A big shout out to Tai, Lena, and Paul, my esteemed colleagues at Safe Software; for answering my questions, making suggestions, fact-checking this article, and generally putting up with my random thoughts about automated casts versus predictability. Tai is also responsible for the “it’s about time” pun. I guess it beats spending all year “working on nothing” (nulls)!
Mark Ireland
Mark, 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)
