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
Zoomdata reads and identifies date and time information a little differently depending on the data source that is connected. Refer to Table 1 for a breakdown of how Zoomdata identifies date and time information in the different data sources:
SUPPORTED DATE AND TIME FORMATS
For data sources including Flat Files, S3, HDFS, Upload API, Twitter, and Kinesis, 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 in 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 "Micro-Queries-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 Pattern option under the Default 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 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 Default column will change to display two selectable drop-down menu lists.
The first menu 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 second field (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.