Date and Time Formats Supported By Zoomdata
Zoomdata supports any valid date and time formats, including the ISO (International Organization for Standardization) 8601 standard. ISO 8601 "... provides an unambiguous and well-defined method of representing dates and times ..." which helps to 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.
Wikipedia "ISO 8601." http://en.wikipedia.org/wiki/ISO_8601
This article 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 walkthrough 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 (Figure 1). 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 DATE AND TIME FIELDS IN ZOOMDATA
You can review the date and time information in the data source's Fields page. Take the following steps to access the Fields page:
- Click the Settings menu item.
- Select your desired data source. We'll use the Cloudera Impala source as the example to illustrate the steps.
Navigate to the
The time-related areas in the Fields page 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. The next topic will walkthrough how to change the field type and modify the time format.
EDITING THE TYPE FIELD AND MODIFYING THE TIME FORMAT TO BE READABLE IN ZOOMDATA
For each Field ID that was identified in your data source, you have the option to edit its Type (as shown in the Step 2 figure below). Zoomdata provides several options for defining the field Type. To access the menu of options for a particular Field ID, select the drop down arrow next to the corresponding Type text. A list of options appears. Take the following steps to edit the field type and to modify the time format.
- To set the field type as a time attribute, select the drop down arrow next to the text
- Scroll down the menu list and select Time .
The Configure column will change to display two selectable drop-down menu lists: Time Pattern and Granularity as well as the Time Zone option.
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.
- Pattern: 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.
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.
- Determine whether you can keep the Default setting or need to enter a customized Pattern.
- Select the unit of time that the chart should use as the default option.
- Save your changes.
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.
- Select the current label (most likely, UTC (UTC+00:00)) to access the Time Zone pane.
- Then you can either (a) manually input the desired time zone in the text field or (b) select the drop down to choose one from the provided list.