Time and Spatial Data: How to work with Datetime

At Safe Software, we talk a lot about the value of adding a spatial component to data. Being able to locate where data is and how it is spatially oriented is helpful for generating insight and visualizing information. So we know “where” is important, but what about “when”?

What is datetime?

Datetime is the attribute representing a feature’s date and time, and is an incredibly valuable attribute in GIS, databases, and many other data types. Consider how much insight can be gained from date and time fields. For example, generating reports based on date ranges, using machine learning to analyze weekly patterns, or visualizing traffic or utility usage over time. The University of Oxford even used GIS to visualize changes in scans of the eye over time, a scenario where time is a crucial attribute.

Another important intersection of “where” and “when” is a tectonic shift. When storing spatial data over time, particularly when storing precise coordinates, it’s worth noting that tectonic plates change the earth at an average rate of about 0.6 inches per year. Storing date and time in addition to coordinates can be essential for accuracy.

Storing datetime fields along with precise coordinates can be important when tectonic plates shift the earth. [image source]

Challenges of working with date and time values

A Datetime field is not just a numeric field, meaning it’s not about storing a timestamp and applying basic arithmetic. Adding a month to the current date isn’t just “today plus 30 days”, and calculating a time window isn’t just about subtracting the difference. Calculations are subject to a lot of variables! Depending on the goal, you might have to consider if it’s a leap year, the time zone the record was saved in, daylight saving time, the international dateline, whether weekends or holidays should be included, and other considerations.

The other big challenge is in aligning formats and dealing with different data structures. That is, different systems and formats represents datetime in different ways. Some are optimized for storage and analysis, while others are designed to be human-readable. For example, ISO 8601 is an international standard for representing date and time data. It arranges the datetime from the largest to the smallest unit of time and has a fixed number of digits, e.g. YYYY-MM-DD, which facilitates sorting. Another system for describing a timestamp is Unix time or Epoch time, which is the number of seconds that have elapsed since January 1st, 1970. The various ways of representing datetimes adds a layer of complexity when translating between databases or other systems and formats.

Translating and transforming datetime in FME

FME is the data integration platform with the best support for spatial data. When you build a data integration workflow, FME can connect to hundreds of different formats and perform transformations to get the results you want. Datetime inputs are processed in a reliable way, and like any other attribute types in FME, can be converted to virtually any format.

The standard FME datetime format is ISO 8601 minus the separator characters, with an optional UTC offset. Precision is supported to up to 9 digits (nanoseconds). For example, “20210801111730.135-08:00” is a date and time with a UTC-8 offset (Pacific Time). When FME reads datetime values, it converts to this internal format for processing. Different types are supported on read and write, including Unix epoch times.

Fun fact: there will be a Unix Y2K in 2038, but FME won’t be affected because we’ve implemented eight-byte time values.

The functionality for working with datetimes in FME is comprehensive and powerful. Our team was meticulous about researching datetime challenges, use cases, and libraries, and included exactly the right components to help data professionals achieve their goals. Datetimes can be stored and processed with nanosecond precision, translated between different standards like ISO and Unix epoch time, and more.

To perform conversions and calculations on datetimes, FME has three key transformers:

The DateTimeConverter converts between date and time formats. This can be useful for making date and time values more human-readable, or to convert human-readable values into a format suitable for storage and analysis.

DateTimeCalculator performs arithmetic on date and time values and intervals. This can be useful in many scenarios, for example, adding or subtracting a certain amount of time from a value, or calculating the time window or difference between two values.

DateTimeStamper adds the date and time as an attribute. The timestamp can be added in local or UTC time, and the value can be modified using the DateTimeCalculator (for example, if you want to set the timestamp for tomorrow).

In addition, more specialized transformers for working with datetimes are available on FME Hub.

FME also offers DateTime functions that can be used wherever expressions are supported. These functions handle timezones and UTC offsets, parsing, manipulating, and formatting date and time values. Functions are useful for simplifying a workspace and including fewer transformers. The datetime processing can be neatly wrapped up into one transformer that supports expressions.

Watch our DateTime webinar for a demonstration and don’t forget to check out our datetime tutorial for a step-by-step guide.

Using this set of transformers and functions is the key to make use of datetimes in FME data integration workflows. Other transformers and functionality in FME can be used to analyze patterns, generate reports, perform Automations, and much more. Try FME for free and check out the links below for more resources.