This article explains how to define a format for date and time strings representation inside the component.
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).
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:
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.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 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).
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 18dd – Day of the month. It is interpreted as numeric in two digits. For example: 02 or 18ddd – Day of the month. It is interpreted as a three letters abbreviation. For example: Weddddd – Day of the month. It is interpreted as the full name. For example: WednesdayM – Month. It is interpreted as numeric in one or two digits. For example: 3 or 11MM – Month. It is interpreted as numeric in two digits. For example: 03MMM – Month. It is interpreted as a three letters abbreviation. For example: MarMMMM – Month. It is interpreted as the full name. For example: Marchyy – Year. It is interpreted as numeric in two digits. For example: 16yyyy – Year. It is interpreted as numeric in four digits. For example: 2016h – 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 12hh – Hour of the day in a 12-hour format [1 – 12]. It is interpreted as numeric in two digits. For example: 01 or 12H – 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 23HH – Hour of the day in a 24-hour format [0 – 23]. It is interpreted as numeric in two digits. For example: 00 or 23m – Minute of the hour [0 – 59]. It is interpreted as numeric in one or two digits. For example: 0 or 59mm – Minute of the hour [0 – 59]. It is interpreted as numeric in two digits. For example: 00 or 59s – Seconds in the minute [0 – 59]. It is interpreted as numeric in one or two digits. For example: 0 or 59ss – Seconds in the minute [0 – 59]. It is interpreted as numeric in two digits. For example: 00 or 59l – Milliseconds. It is interpreted as numeric in three digits. For example: 100L – Milliseconds. It is interpreted as numeric in two digits (rounded, if needed). For example: 10t – am/pm one letter indicator. For example: a or ptt – am/pm two letters indicator. For example: am or pmT – AM/PM one letter indicator. For example: A or PTT – AM/PM two letters indicator. For example: AM or PMUTC: – indicates that UTC time zone should be used. For example: UTC:dd/MM/yyyy HH:mm:ssGMT+-N: – indicates your custom timezone, where N can be changed from 1 to 12. For example: GMT+6:dd/MM/yyyy HH:mm:ssHere 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"
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.
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.