Dear visitor, despite the COVID-19 outbreak, our team continues operating at full speed.

Also, here is the form where you can apply for a special discount and we will contact you with possible options. Stay safe and continue achieving your business goals.

Fill the form
Get Free Trial
Get Free Trial
  1. API reference
  2. Welcome
    1. Component overview
    2. Quick start
    3. System requirements
    4. Troubleshooting
    5. Managing license keys
    6. Migrating from WebDataRocks to Flexmonster
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Connecting to JSON using the custom data source API
      3. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Connecting to CSV using the custom data source API
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to a MySQL database
      3. Connecting to a Microsoft SQL Server database
      4. Connecting to a PostgreSQL database
      5. Connecting to an Oracle database
      6. Connecting to other databases
    4. MongoDB
      1. Introduction to the Flexmonster MongoDB Connector
      2. Getting started with the MongoDB Connector
      3. Embedding the MongoDB Connector into the server
    5. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with the Accelerator
      3. Installing the Accelerator as a Windows service
      4. Referring the Accelerator as a DLL
      5. Configuring the authentication process
      6. Configuring a secure HTTPS connection
      7. Troubleshooting
    6. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with the Accelerator
      3. Configuring Mondrian roles
      4. Configuring username/password protection
      5. Configuring a secure HTTPS connection
      6. Troubleshooting
    7. Elasticsearch
      1. Connecting to Elasticsearch
      2. Configuring the mapping
    8. Custom data source API
      1. Introduction to the custom data source API
      2. A quick overview of a sample Node.js server
      3. A quick overview of a sample .NET Core server
      4. Implementing the custom data source API server
      5. Implementing filters
      6. Returning data for the drill-through view
      7. Supporting more aggregation functions
  4. Security
    1. Security in Flexmonster
    2. Security aspects of connecting to an OLAP cube
      1. Ways of connecting to an OLAP cube
      2. The data transfer process
      3. Data security
      4. Data access management
  5. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Mapping
    6. Number formatting
    7. Conditional formatting
    8. Set the report for the component
    9. Get the report from the component
    10. Date and time formatting
    11. Configuring global options
    12. Export and print
    13. Calculated values
    14. Custom sorting
  6. Integration with frameworks
    1. Available tutorials
    2. Integration with AngularJS (v1.x)
    3. Integration with Angular
    4. Integration with React
    5. Integration with React Native
    6. Integration with Vue
    7. Integration with Python
      1. Integration with Django
      2. Integration with Jupyter Notebook
    8. Integration with R Shiny
    9. Integration with Webpack
    10. Integration with ASP.NET
    11. Integration with jQuery
    12. Integration with JSP
    13. Integration with TypeScript
    14. Integration with RequireJS
    15. Integration with PhoneGap
  7. Integration with charts
    1. Integration with Highcharts
    2. Integration with Google Charts
    3. Integration with FusionCharts
    4. Integration with any charting library
  8. Customizing
    1. Customizing the Toolbar
    2. Customizing appearance
    3. Customizing the context menu
    4. Localizing the component
  9. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.7 to 2.8
    4. Migration guide from 2.6 to 2.7
    5. Migration guide from 2.5 to 2.6
    6. Migration guide from 2.4 to 2.5
    7. Migration guide from 2.3 to 2.4
    8. Migration guide from 2.2 to 2.3
    9. Documentation for older versions
Table of contents

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:

  1. datePattern is used to format “date string” date fields ("type": "date string" in JSON, "ds+" prefix in CSV). Default pattern string: "dd/MM/yyyy".
  2. 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".
  3. 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: