Supported Date and Time Formats
Zoomdata supports any valid date and time formats, including the ISO (International Organization for Standardization) 8601 standard. ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601) provides a method for representing dates and times that help avoid misinterpretation of numeric representations of dates and times, especially across countries using different conventions. Zoomdata supports time based on both the 12- and 24-hour timekeeping systems and can read timezone information. In addition, Zoomdata can also interpret UNIX timestamp and time represented in milliseconds.
This topic details how Zoomdata handles date and time information in data sources and provides a comprehensive list of date and time formats supported by Zoomdata. We’ll walk through how administrators and users with edit rights (to data sources) can access the data fields in Zoomdata and edit the field "Type" to a specific time format. Zoomdata offers the flexibility to modify a field’s “Type” so that a specific time format can be provided.
How Zoomdata Handles Date and Time Data
To define the time and date for the data sources, Zoomdata checks the field type set in the data source. Zoomdata will define the field from your data source as Time in the following cases:
- if the field type is timestamp
- if the field type is UNIX timestamp
- if a numeric field contains year value
- If a field type is a string, however it matches one of the patterns listed below.
In other cases, you need to set the field type to Time and specify a corresponding time pattern. Keep in mind that the group-by functionality will not work for these fields. However, you can use them in filters and apply them as filters on the time bar.
It may occur that when you apply filters the results visualized on your chart will be incorrect. This may happen due to the manually configured time pattern since the converted values must match the dates order.
Consider the following example.
The string "20160801" matches the following pattern: "yyyyMMdd". However, in case the string is "08012016" the pattern will be "MMddyyyy".
While filtering the time data by these fields, the time values are considered as numbers, so when filtered descending, "09012015" will go before "08012016". In this case, the visualization will not be correct.
Supported Date and Time Formats
For data sources including Flat Files, S3, HDFS, and Upload API the following standard date and time formats are recognized by Zoomdata:
- yyyy-MM-dd HH:mm:ss
- yyyy-MM-dd HH:mm:ss.SSS
- yyyy-MM-dd HH:mm:ss,SSS
- MM/dd/yyyy HH:mm:ss
- MM/dd/yy h:mm a
- MM/dd/yy HH:mm:ss
- MM/dd/yy HH:mm
- MM/dd/yy hh:mm a
- MMM dd, yyyy hh:mm a
- MMM dd yyyy HH:mm:ss
- dd/MM/yyyy hh:mm:ss a
- dd/MM/yy hh:mm:ss a
- unix timestamp
- time as milliseconds
However, there are limitations to be aware of for some of these standards:
- For fields containing a four digit year (that is, yyyy), Zoomdata recognizes only a limited range of years, specifically, from 1800 through 2020.
- For fields containing the Unix Timestamp format, Zoomdata is able to read only a limited range of years, specifically, from 1990 through 2020.
Upon successful connection with a data source, Zoomdata displays the results on the Fields page (as shown in Figure 1). Date and time information will be available via either the Timestamp (_ts) column (1) or identified in the Type descriptor (2). Note that if the data source does not provide a Timestamp column, then the admin can edit the Type column to specify a Time attribute.
Accessing the Data and Time Fields in Zoomdata
Select the data source configuration from the list.
Select the Fields tab of the data source configuration.
The time-related areas on the Fields tab include:
- The column (field ID) serving as the timestamp.
- The Type attribute where a field ID can be set to Time.
If your data source contains a timestamp column, Zoomdata will attempt to use the default format established in the data source. However, you have the ability to edit the default setting. This flexibility is helpful when the time format is not one in which Zoomdata can recognize. Steps to modify the format can be found in the topic below Modifying the Time Format.
If your data source also includes additional time-related attributes, these fields can also be edited as needed and the specific time format can be set using the Time Pattern option under the Configure column.
Keep in mind that as long as any column contains numbers, you have the option to set its type to Time. Then you can set the Time Pattern to a time format that can be recognized by Zoomdata.
Setting Time Field Types and Modifying the Time Format
Perform following steps to identify a field as a time filed and modify its time format.
Change the value in the Type column for the field to Time.
The Configure column changes to display two selectable drop-down menu lists: Time Pattern and Granularity as well as the Time Zone option.
Specify the time pattern for the field. The Time Pattern list (with Default displayed) provides two options:
- Default: Select this option if the time format in the data source conforms to one of the standards recognized by Zoomdata.
- Custom: Select this option when you need to enter a unique time format. A new text box will appear, allowing you to specify the unique time format.
Specify a granularity for the time field. The Granularity list (with the Second unit of time displayed) lets you select the default unit of time to start with for the data source.
Specify the time zone for the time field. The Time Zone option lets you select a time zone: The time zone label will be displayed in charts and dashboards for this data source.