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.
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.
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). The default pattern string is "dd/MM/yyyy".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".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").
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:
"yyyy-MM-dd"
"HH:mm:ss"
"yyyy-MM-dd'T'HH:mm:ss"
"MMMM d, yyyy"
"h:mm TT"
"h:mm:ss TT"
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.
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.
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.
You may be interested in the following articles: