1. API reference
  2. Welcome
    1. Component overview
    2. Quick start
    3. System requirements
    4. Troubleshooting
    5. Managing license keys
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Connecting to JSON using the Data Compressor
      3. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Connecting to CSV using the Data Compressor
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to other databases
      3. Connecting to a database with Node.js
      4. Connecting to a database with .NET
      5. Connecting to a database with .NET Core
      6. Connecting to a database with Java
      7. Connecting to a database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with the Accelerator
      3. Installing the Accelerator as a Windows service
      4. Referring the Accelerator as a DLL
      5. Configuring the authentication process
      6. Configuring a secure HTTPS connection
      7. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with the Accelerator
      3. Configuring Mondrian roles
      4. Configuring username/password protection
      5. Configuring a secure HTTPS connection
      6. Troubleshooting
    6. Elasticsearch
      1. Connecting to Elasticsearch
      2. Configuring the mapping
  4. Security
    1. Security in Flexmonster
    2. Security aspects when connecting to an OLAP cube
      1. The data transfer process
      2. Data security
      3. Data access management
    3. Security aspects when connecting to a database
      1. The data transfer process
      2. Data access management
  5. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set the report for the component
    8. Get the report from the component
    9. Date and time formatting
    10. Configuring global options
    11. Export and print
    12. Calculated values
    13. Custom sorting
  6. Integration with frameworks
    1. Available tutorials
    2. Integration with AngularJS (v1.x)
    3. Integration with Angular
    4. Integration with React
    5. Integration with Webpack
    6. Integration with ASP.NET
    7. Integration with jQuery
    8. Integration with JSP
    9. Integration with TypeScript
    10. Integration with RequireJS
    11. Integration with PhoneGap
  7. Integration with charts
    1. Integration with Highcharts
    2. Integration with Google Charts
    3. Integration with FusionCharts
    4. Integration with any charting library
  8. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Customizing context menu
    4. Localizing component
  9. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.6 to 2.7
    4. Migration guide from 2.5 to 2.6
    5. Migration guide from 2.4 to 2.5
    6. Migration guide from 2.3 to 2.4
    7. Migration guide from 2.2 to 2.3
    8. Documentation for older versions
Table of contents

Date and time formatting

This article explains how to define the format for date and time string representation inside the component. Note that the approach below is only available for "ocsv", "csv", and "json" data source types.

If the component is connected to an OLAP cube, you can configure date and time formatting inside the cube.

If the component is connected to Elasticsearch, date and time formatting should be configured with the help of index mapping.

Input date format

As an input date format, the pivot table component supports the ISO 8601 date format, for example: "2016-03-20" (just date) or "2016-03-20T14:48:00" (date and time). Other formats aren’t officially supported and may have unexpected results.

Representation format

The format for the representation of dates and times inside the component differs from the input format. The component supports pattern strings to format date and time data. There are three properties of a report that format date and time strings:

  1. datePattern is used to format “date string” date fields ("type": "date string" in JSON, "ds+" prefix in CSV). The default pattern string is "dd/MM/yyyy".
  2. dateTimePattern is used to format “datetime” date fields ("type": "datetime" in JSON, "dt+" prefix in CSV). The default pattern string is "dd/MM/yyyy HH:mm:ss".
  3. timePattern is used to format “time” date fields ("type": "time" in JSON, "t+" prefix in CSV). The default pattern string is "HH:mm:ss". For more details refer to the time pattern section.

To change the default date and time format for “date string” date fields, specify datePattern in the report explicitly. Similarly for “datetime” and “time” date fields, specify dateTimePattern and timePattern in the report respectively.

Here is how to define datePattern in the report:

{
	dataSource: {
		dataSourceType: "json",
		data: [
		{
			"date":{"type":"date string"},
			"n":{"type":"number"}
		},
		{
			"date":"2016-04-06T23:59:30",
			"n":1
		},
		{
			"date":"2016-04-06T23:59:30",
			"n":1
		},
		{
			"date":"2016-02-07T20:33",
			"n":1
		}
		]
	},
	options: {
		datePattern: "yyyy-MM-dd HH:mm:ss"
	},
	slice: {
		rows: [{ uniqueName: "date" }],
		columns: [{ uniqueName: "[Measures]" }],
		measures: [{ uniqueName: "n" }],
	}
}

Test or modify this example here.

The same can be done for dateTimePattern and timePattern in the report. Note that both datePattern and dateTimePattern can contain the time part.

The user’s local time zone is used in the date by default. If you want to use another time zone, include "GMT+-N:" at the beginning of the pattern (e.g. "GMT+6:dd/MM/yyyy HH:mm:ss").

Pattern syntax

A pattern contains sequences of letters that are replaced with date and time values in the formatted string. For example, in the pattern "yyyy/MM" the characters "yyyy" are replaced with a four-digit year, followed by a "/" character, and the characters "MM" are replaced with a two-digit month.

The following list describes all the valid pattern letters and their meanings:

  • d – Day of the month. It is represented as a one or two digit number. For example: 2 or 18.
  • dd – Day of the month. It is represented as a two digit number. For example: 02 or 18.
  • ddd – Day of the week. It is represented as a three letter abbreviation of the day of the week. For example: Wed.
  • dddd – Day of the week. It is represented as the full name of the day of the week. For example: Wednesday.
  • M – Month. It is represented as a one or two digit number. For example: 3 or 11.
  • MM – Month. It is represented as a two digit number. For example: 03.
  • MMM – Month. It is represented as a three letter abbreviation of the name of the month. For example: Mar.
  • MMMM – Month. It is represented as the full name of the month. For example: March.
  • yy – Year. It is represented as a two digit number. For example: 16.
  • yyyy – Year. It is represented as a four digit number. For example: 2016.
  • h – Hour of the day using the 12-hour format [1 – 12]. It is represented as a one or two digit number. For example: 1 or 12.
  • hh – Hour of the day using the 12-hour format [1 – 12]. It is represented as a two digit number. For example: 01 or 12.
  • H – Hour of the day using the 24-hour format [0 – 23]. It is represented as a one or two digit number. For example: 0 or 23.
  • HH – Hour of the day using the 24-hour format [0 – 23]. It is represented as a two digit number. For example: 00 or 23.
  • k – Hour of the day using the 24-hour format [1 – 24]. It is represented as a one or two digit number. For example: 1 or 24.
  • kk – Hour of the day using the 24-hour format [1 – 24]. It is represented as a two digit number. For example: 01 or 24.
  • m – Minutes [0 – 59]. It is represented as a one or two digit number. For example: 0 or 59.
  • mm – Minutes [0 – 59]. It is represented as a two digit number. For example: 00 or 59.
  • s – Seconds [0 – 59]. It is represented as a one or two digit number. For example: 0 or 59.
  • ss – Seconds [0 – 59]. It is represented as a two digit number. For example: 00 or 59.
  • l – Milliseconds. It is represented as a three digit number. For example: 100.
  • L – 10 Milliseconds. It is represented as a two digit number (rounded, if needed). For example: 10.
  • t – am/pm – a one letter indicator. For example: a or p.
  • tt – am/pm – a two letter indicator. For example: am or pm.
  • T – AM/PM – a one letter indicator. For example: A or P.
  • TT – AM/PM – a two letter indicator. For example: AM or PM.
  • UTC: – indicates that the UTC time zone should be used. Example format: "UTC:dd/MM/yyyy HH:mm:ss".
  • GMT+-N: – indicates the time zone to use, where N can be changed from 1 to 12. Example format: "GMT+6:dd/MM/yyyy HH:mm:ss".

Here are several common date and time formats:

  • ISO date – "yyyy-MM-dd"
  • ISO time – "HH:mm:ss"
  • ISO date and time – "yyyy-MM-dd'T'HH:mm:ss"
  • long date – "MMMM d, yyyy"
  • short time – "h:mm TT"
  • long time – "h:mm:ss TT"

“date string” and “datetime” data types

Here is an explanation of the difference between “date string” and “datetime” data types.

“date string” is a date field that can be used for rows, columns, or report filters in the pivot table when you want to represent a date as a string. The members of such a field are formatted using the datePattern and are sorted inside the component as dates.

“datetime” is a date field that can be used for values in the pivot table. Min, max, count, and distinct count aggregations can be applied to this field. The aggregated date/time strings in the values of the cells in the pivot table are formatted using the dateTimePattern.

Managing time zones

Flexmonster allows setting the time zone in both datePattern and dateTimePattern which will affect the time displayed on the grid. For example, you can set the pattern as "GMT+1:dd/MM/yyyy", where your custom time zone can be set by specifying GMT+-N hours. N can be changed from 1 to 12. Setting the time zone affects only the date representation, the original data is not changed. Check out a live sample on JSFiddle.

If there is a necessity to set a timezone for hierarchical dates (for JSON: "date", "year/month/day", and "year/quarter/month/day"; for CSV: "d+", "D+", and "D4+"), a special dateTimezoneOffset property should be used. See a live demo on JSFiddle.

In Flexmonster Data Compressor for Java and Flexmonster Data Compressor for PHP, it is possible to specify the time zone to parse the dates. It would be used instead of the default (UTC). Note: the data is still returned in UTC. Use Compressor.timeZone(Java) or Compressor::TIME_ZONE(PHP) to set the time zone for parsing the data in the Data Compressor. The time zone should be specified using the standard format for the chosen technology.

Time pattern

To change the default time format for “time” date fields, timePattern needs to be specified explicitly in the report:

options: {
  timePattern: "d'd' HH'h' mm'min'"
}

Check out a full example on JSFiddle.

List of supported patterns:

  • d – Days. It is represented as a one or two digit number. For example: 5 or 11.
  • dd – Days. It is represented as a two digit number. For example: 05 or 11.
  • H – Hours. It is represented as a one or two digit number. For example: 6 or 15.
  • HH – Hours. It is represented as a two digit number. For example: 06 or 15.
  • HHH – Hours. Displays time data in hours even when the number of hours is greater than 24. For example: 46 or 15.
  • m – Minutes. It is represented as a one or two digit number. For example: 2 or 10.
  • mm – Minutes. It is represented as a two digit number. For example: 02 or 10.
  • s – Seconds. It is represented as a one or two digit number. For example: 3 or 16.
  • ss – Seconds. It is represented as a two digit number. For example: 03 or 16.

The time should be specified in seconds in the data source. Example of time specified in CSV file:

t+time
121

With default formatting, this time will be displayed as 00:02:01 inside Flexmonster.

What’s next?

You may be interested in the following articles: