DHTMLX Pivot. Date format example
Demo of date format configuration in DHTMLX Pivot. Set the initial date display format via locale and switch between formats dynamically using setConfig for flexible date presentation.
Live example
function setFormat(value){
widget.setConfig({locale: {formats: {dateFormat: value}}});
}
// date string to Date
const dateFields = fields.filter(f => f.type == "date");
if (dateFields.length) {
dataset.forEach(item => {
dateFields.forEach(f => {
const v = item[f.id];
if (typeof v == "string") item[f.id] = new Date(v);
});
});
}
const widget = new pivot.Pivot("#pivot", {
locale: {formats: { dateFormat: "%d %M %Y %H:%i" }},
fields,
data: dataset,
config: {
rows: [
"state"
],
columns: [
"product_line",
"product_type"
],
values: [
{
field: "date",
method: "min"
},
{
field: "profit",
method: "sum"
},
{
field: "sales",
method: "sum"
}
]
}
});
<!-- auxiliary controls for interacting with the sample -->
<link rel="stylesheet" href="https://snippet.dhtmlx.com/codebase/assets/css/auxiliary_controls.css">
<section class="dhx_sample-controls">
<select name="form-select" class="dhx_select dhx_sample-input" id="select" onchange="setFormat(this.value)" style="width: 250px;">
<option value="%d %M %Y %H:%i" selected>DD Month YYYY HH:MM</option>
<option value="%m.%d.%Y %h:%i"">MM.DD.YYYY hh:MM</option>
<option value="%M-%d-%y %h:%i">MM-DD-YY hh:MM</option>
<option value="%d/%m/%Y %h:%i">DD/MM/YYYY hh:MM</option>
</select>
</section>
<!-- component container -->
<div id="pivot" style="height: calc(100% - 60px); width: 100%"></div>
<!-- dataset -->
<script>
const dataset = [
{
"cogs": 51,
"date": "10/1/2018",
"inventory_margin": 503,
"margin": 71,
"market_size": "Major Market",
"market": "Central",
"marketing": 46,
"product_line": "Leaves",
"product_type": "Herbal Tea",
"product": "Lemon",
"profit": -5,
"sales": 122,
"state": "Colorado",
"expenses": 76,
"type": "Decaf"
},
{
"cogs": 52,
"date": "10/1/2018",
"inventory_margin": 405,
"margin": 71,
"market_size": "Major Market",
"market": "Central",
"marketing": 17,
"product_line": "Leaves",
"product_type": "Herbal Tea",
"product": "Mint",
"profit": 26,
"sales": 123,
"state": "Colorado",
"expenses": 45,
"type": "Decaf"
},
// ... 1060 more items (see Live Editor for full data)
];
const fields = [
{
"id": "cogs",
"label": "Cogs",
"type": "number"
},
{
"id": "date",
"label": "Date",
"type": "date"
},
{
"id": "inventory_margin",
"label": "Inventory Margin",
"type": "number"
},
{
"id": "margin",
"label": "Margin",
"type": "number"
},
{
"id": "market_size",
"label": "Market Size",
"type": "text"
},
{
"id": "market",
"label": "Market",
"type": "text"
},
{
"id": "marketing",
"label": "Marketing",
"type": "number"
},
{
"id": "product_line",
"label": "Product Line",
"type": "text"
},
{
"id": "product_type",
"label": "Product Type",
"type": "text"
},
{
"id": "product",
"label": "Product",
"type": "text"
},
{
"id": "profit",
"label": "Profit",
"type": "number"
},
{
"id": "sales",
"label": "Sales",
"type": "number"
},
{
"id": "state",
"label": "State",
"type": "text"
},
{
"id": "expenses",
"label": "Expenses",
"type": "number"
},
{
"id": "type",
"label": "Type",
"type": "text"
}
];
</script>Date fields in pivot tables can appear in many formats depending on the audience and locale. A US-focused report might show "10/25/2018" while a European report needs "25.10.2018". When date values are used as aggregated results (min, max, average), the display format becomes even more important for readability. Hardcoding a single format limits the pivot's usefulness across different contexts.
DHTMLX Pivot supports date formatting through the locale configuration. The formats.dateFormat property accepts a format string using directives like %d (day), %M (full month name), %Y (4-digit year), %H (24-hour), and %i (minutes). You can set the format at initialization and change it at runtime using widget.setConfig(), which re-renders the pivot with the new format applied to all date cells.
This example demonstrates dynamic date format switching on a coffee sales dataset. A dropdown selector lets users choose between four date formats: "DD Month YYYY HH:MM", "MM.DD.YYYY hh:MM", "MM-DD-YY hh:MM", and "DD/MM/YYYY hh:MM". The pivot displays Date (Min), Profit (Sum), and Sales (Sum) grouped by state and product categories, with dates reformatted instantly when the selection changes.
Solution overview
To configure the date format, set the locale.formats.dateFormat property in the Pivot constructor. To change it dynamically, call widget.setConfig() with the new format string. A setFormat() helper function wired to an HTML select element triggers the update.
The HTML section includes a dropdown to switch formats:
Key points
- Locale-based formatting: Date formats are configured through
locale.formats.dateFormat, keeping formatting separate from data and configuration logic - Runtime switching with setConfig: Call
widget.setConfig({ locale: { formats: { dateFormat: value } } })to change the format at runtime - the pivot applies the updated locale config immediately - Format directives: Use
%d(day),%m(month number),%M(month name),%y(2-digit year),%Y(4-digit year),%H(24h),%h(12h),%i(minutes) to compose any date pattern - Works with date aggregations: The format applies to all date values in the table, including results of min, max, and other date-type aggregation methods
API reference
- Pivot locale: Configuration for locale settings including date and number formats.
- Pivot setConfig(): Method to update pivot configuration at runtime (the component is recreated with updated config).
- Pivot configuration: Properties for defining rows, columns, and value aggregations.