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