Date and time formatting

This article explains how to define the format for date and time string representations inside the component. Note that the approach below is only available for "csv" and "json" data sources.

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, you can configure date and time formatting with the help of an 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:

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

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: {
		type: "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 you need to set a timezone for hierarchical dates (for JSON: "date", "year/month/day", and "year/quarter/month/day"; for CSV: "d+", "D+", and "D4+"), use the dateTimezoneOffset property. See a live demo on JSFiddle.

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: