The Datetime data type stores an instant in time expressed as a calendar date and time of day. How it is stored can range from a year to a fraction of a second. Datetime is often an attribute in data that represents a features date and time which can be used to report on or visualise data through time or understand patterns. ISO 8601 is the international standard for date and time representation where the structure is largest to smallest unit of time e.g. YYYY-MM-DD. FME has its own standard format which is ISO 8601 without the separator characters and an optional offset.
Datetime data can be complicated to work with but has great value to an organization. Many systems and data sources have datetime fields which can be used to extend the value of your data, delivering better quality reporting and data analysis.
Commonly there are multiple Datetime structures from different data sources or applications that need to be formatted, but when taking into account further complexities such as time zones, day light savings or leap years it can be difficult to make comparisons.
The type of complexities when dealing with datetimes can range between Environmental/Spatial (time zones), Application based (format alignment) or societal (holidays, weekends/weekdays) factors.
When working with Datetime and FME, common questions include;
- can datetimes be formatted to be human readable
- can they be recorded into a database, what structure or precision can be used, and does it include the time zone or can you just use UTC
- does it matter where a server is located, is it local time or the same, is it updated to Day Light Saving
- is a date older than six months or can you compare if a date is older than another date
- what’s the time difference in days, hours, minutes or seconds
- Is the date a weekday, is it a weekday in 12 days, what number day in the year is it
Whatever you are trying to achieve with your data FME can help answer these questions with the ability to convert, calculate and analyse date time fields. As FME reads in data and it converts it to its internal format for processing and with a few exceptions writes it out to the appropriate format (some supported formats require specific date time syntax). Working with Datetime in FME, there are three main transformers which make dealing with date time easier:
DateTimeStamper – adds a timestamp to a feature in either local or UTC time, the output can be used in datetime calculations or modified within your workflow
DateTimeConverter – converts datetime attributes from one format to another and the input can contain a date, time, datetime or Unix epoch time. Conversions can be carried out, so it is human readable or into formats suitable for reporting, analysis, or storage
DateTimeCalculator – performs arithmetic on date, time, datetime and intervals which can be used to add or subtract time from values, calculate the difference between values or calculate time windows as well as the ability to use advanced expressions to carry out composite expressions
On the FME hub you will also find a series of Datetime transformers that are available for more specialised transformers. DateTime functions can also be used anywhere in a workspace that expressions are supported, which helps to save space in a workspace and helps to optimise your workspace by wrapping up a lot of functionality into one transformer. The DateTimeConverter has a quick reference guide in FME Form of datetime functions with a more comprehensive guide online.
A new transformer to FME 2021 is the TimeWindower and has been designed for real-time data streams but can also be used for time-based processing or reporting. Features are grouped by assigning ID’s based on the time a feature arrives at the transformer or by a timestamp to each feature. Once your data is in groups it is ready for downstream processing or analysis. It can be used for streamed data but also existing data.
With FME you have the ability to work with Datetime fields in your data integration workflows eliminating the need for complex coding and improving the quality value of your data.
Credits:
Safe Software Webinar | Making Use of DateTime
Safe Software Blog | Time and Spatial Data: How to work with Datetime
