Zoomdata Version

Date and Time Formats Supported By Zoomdata

OVERVIEW

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[1] ..." 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.
[1]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:

Table 1

Type Data Sources Description
Flat files and Streaming data sources

For these data sources, Zoomdata determines whether numeric-based data in a field conforms to a standard time format (that is, ISO 8601). If so, Zoomdata sets the 'Type' field to 'Time' (see Supported Date and Time Formats below for a comprehensive list).

In addition, admins and users with edit rights can manually specify a custom time format (which should still comply with ISO 8601 format) on the Fields page (but this must be done before the data file is uploaded into Zoomdata).

SQL data sources

For these 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 (seconds or milliseconds)
  • if a numeric field contains just a year value

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.


Figure 1

NoSQL data Sources

Zoomdata will define the field from these data sources as 'Time' in the following cases:

  • if the field type is timestamp
  • if the field type is UNIX timestamp (seconds or milliseconds)
  • if a numeric field contains just a year value
Spark

For these data sources, Zoomdata will define the field type based on internal field mapping criteria.

For example, if your data source contains fields with text strings such as 'Updated', 'Due date', or 'Date', Zoomdata recognizes them as time fields.

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
  • yyyy-MM-dd'T'HH:mm:ss
  • yyyy-MM-dd'T'HH:mm:ssZ
  • yyyy-MM-dd'T'HH:mm:ss.SSS
  • yyyy-MM-dd'T'HH:mm:ss.SSSZ
  • 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
  • yyyy-MM-dd
  • MM/dd/yy
  • MM/dd/yyyy
  • yyyy
  • 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:

  1. Click the Settings menu item.
  2. Click the Sources .
  3. Select your desired data source. We'll use the "Micro-Queries-Impala" source as the example to illustrate the steps.
  4. Navigate to the Fields page.

Figure 2

  1. 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.

  1. To set the field type as a time attribute, select the drop down arrow next to the text
  2. Scroll down the menu list and select "Time."

Figure 3

The Default column will change to display two selectable drop-down menu lists.

Figure 4

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.

  1. Determine whether you can keep the "Default" setting or need to enter a customized "Pattern".
  2. Select the unit of time that the chart should use as the default option.
  3. Save your changes.