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. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Connecting to CSV using Compressor
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to other databases
      3. Connecting to database with Node.js
      4. Connecting to database with .NET
      5. Connecting to database with .NET Core
      6. Connecting to database with Java
      7. Connecting to database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with Accelerator
      3. Installing Accelerator as a Windows Service
      4. Referring Accelerator as a DLL
      5. Configuring authentication process
      6. Configuring secure HTTPS connection
      7. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with Accelerator
      3. Configuring Mondrian roles
      4. Сonfiguring username/password protection
      5. Сonfiguring secure HTTPS connection
      6. Troubleshooting
    6. icCube
  4. Security
    1. Accelerator security
    2. Data Compressor Security
  5. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set report to the component
    8. Get 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.5 to 2.6
    4. Migration guide from 2.4 to 2.5
    5. Migration guide from 2.3 to 2.4
    6. Migration guide from 2.2 to 2.3
    7. Documentation for older versions
Table of contents

Date and time formatting

This article explains how to define a format for date and time strings representation inside the component.

Input date format

As an input date format, pivot table component supports ISO 8601 date (other formats may be used, but results can be unexpected). For example, "2016-03-20" (just date) or "2016-03-20T14:48:00" (date and time).

Representation format

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

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

In order to change the default date and time format for “date string” date fields, you need to specify datePattern in the report explicitly. The same is for “datetime” date fields format, so you need to specify dateTimePattern in the report.

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 in the report. Please note that both patterns datePattern and dateTimePattern can contain time part.

Also, user’s local time zone is used to represent the date by default. If you want to use anozer time zone, please include GMT+-N: part at the beggining of the pattern (i.e. GMT+6:dd/MM/yyyy HH:mm:ss).

Patterns syntax

The 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 the valid pattern letters and their meaning:

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

In addition, we would like to explain 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 date as a string. The members of such a field are formatted using datePattern and 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 it. The aggregated date/time strings in values cells in the pivot table are formatted using dateTimePattern.

Managing time zones

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

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

Time pattern

To change the default time format, timePattern needs to be specified explicitly in the report:

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

Open the full example on JSFiddle.

List of supported patterns:

  • d – Number of days. It is interpreted as numeric in one or two digits. For example: 5 or 11
  • dd – Number of days. It is interpreted as numeric in two digits. For example: 05 or 11
  • H – Number of hours. It is interpreted as numeric in one or two digits. For example: 6 or 15
  • HH – Number of hours. It is interpreted as numeric in two digits. For example: 06 or 15
  • m – Number of minutes. It is interpreted as numeric in one or two digits. For example: 2 or 10
  • mm – Number of minutes. It is interpreted as numeric in two digits. For example: 02 or 10
  • s – Number of seconds. It is interpreted as numeric in one or two digits. For example: 3 or 16
  • ss – Number of seconds. It is interpreted as numeric in two digits. For example: 03 or 16

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

t+time
121

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

What’s next?