Menu
Download Free Trial
  1. API reference
  2. Welcome
    1. Getting started
    2. Quickstart
    3. System requirements
    4. Typical errors
    5. Installation troubleshooting
  3. Connecting to Data Source
    1. JSON
      1. JSON data source
      2. Data types in JSON
    2. CSV
      1. CSV data source
      2. Data types in CSV
    3. SQL database
      1. Connecting to SQL database
      2. Connecting to database with .NET
      3. Connecting to database with .NET Core
      4. Connecting to database with Java
      5. 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. Configuring username/password protection
      5. Configuring secure HTTPS connection
      6. 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. Configuring report
    1. Configuring 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. Calculated values
    10. Custom sorting
    11. Date and time formatting
    12. Configuring global options
  5. Integration with frameworks
    1. Integration with frameworks
    2. Integration with AngularJS
    3. Integration with Angular 2
    4. Integration with Angular 4
    5. Integration with React
    6. Integration with RequireJS
    7. Integration with TypeScript
    8. Integration with ASP.NET
    9. Integration with JSP
    10. Integration with PhoneGap
  6. Integration with charts
    1. Integration with charts
    2. Integration with Highcharts
    3. Integration with FusionCharts
    4. Integration with Google Charts
    5. Integration with any charting library
  7. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Localizing component
  8. Export and print
  9. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.3 to 2.4
    4. Migration guide from 2.2 to 2.3
  10. Managing license keys
  11. Older Versions
    1. Documentation 2.3
    2. Documentation 2.2
    3. API reference - Flex
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.

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 it is required to use UTC time zone, please include UTC: part at the beggining of the pattern (i.e. UTC: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
  • 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

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.