Date and time formatting

This tutorial explains how to format date and time fields when using JSON, CSV, Flexmonster Data Server, MongoDB, and the custom data source API. If using another data source:

• For Microsoft Analysis Services, Flexmonster uses date formatting configured inside the cube.
• For Elasticsearch, see the Formatting dates in Elasticsearch section.

Note Learn about input date formats that Flexmonster supports for JSON, CSV, Flexmonster Data Server (JSON and CSV), and the custom data source API.

Overriding the default date type

By default, date fields have the "date" type for JSON and CSV, and the "date string" type for Flexmonster Data Server, MongoDB, and the custom data source API.

You can override the default date type for all date fields in the dataset (supported only for JSON and CSV) or for specific fields. If you want to apply formatting to date fields, use the following types:

• "date string"
• "datetime"

Learn more about available date types in our video overview:

Set a type for all date fields in the dataset (only for JSON and CSV)

To change the default type for all date fields, use the options.defaultDateType property:

report: {
  options: {
    defaultDateType: "date string"
  }
}

The "date string" type will now be applied to all date fields in the dataset Live example. Check out all available date types.

Note The defaultDateType option works only for "json" and "csv" data source types.

Set a type for specific date fields

To change the default type for a specific date field, use the mapping. Learn how to define the type of a field in the mapping.

Check out all available date types.

Formatting date fields

In Flexmonster, "date string" and "datetime" fields are formatted using pattern strings. Formatting can be applied to all fields of a specific date type or to each field separately.

Format date fields by type

To format fields of the "date string" type, use the options.datePattern property, and to format fields of the "datetime" type, use the options.dateTimePattern property:

report: {
  options: {
    datePattern: "yyyy.MM.dd",
    dateTimePattern: "yyyy.MM.dd HH:mm TT"
  },
  dataSource: {
    data: [
      // ...
    ],
    mapping: {
      "Date1": {
        type: "date string"
      },
      "Date2": {
        type: "datetime"
      }
    }
  }
}

Live example

Format specific date fields

To format a specific field of the "date string" or "datetime" type, use the format property in the mapping. The format overrides other formatting options, such as datePattern and dateTimePattern.

Here is an example of how to define formatting for a certain field:

report: {
  options: {
    dateTimePattern: "MMM d, yyyy, h:mm:ss TT",
    // ...
  },
  dataSource: {
    data: [
      // ...
    ],
    mapping: {
      "OrderDateTime": {
        type: "datetime",
        format: "GMT+6:dd/MM/yyyy HH:mm:ss",
      }
    }
  }
}

Live example

In the example above, dateTimePattern is ignored for the OrderDateTime field because the format property in the mapping overrides the format from options.

Supported date patterns

See the list of the supported patterns for "date string" and "datetime" fields:

Examples of "date string" and "datetime" formats:

Note For JSON and CSV data sources, "date string" fields are rounded down with the "1d" interval by default (e.g., "04/25/2021T21:30:05" is rounded down to "04/25/2021T00:00:00"). See how to change the interval.

Managing time zones

By default, Flexmonster uses the user’s local time zone for dates.

For example, your dataset contains a date with the GMT+2 time zone: "2021-04-25T21:30:05+02:00". Flexmonster will display this date according to your local time zone Live example.

If needed, you can set another time zone:

• For all date fields
• For "date string" and "datetime" fields
• For specific date fields

Note Setting a time zone in the component affects only date representation; the original data is not changed.

Set a time zone for all date fields

To set a timezone for all date fields, use the options.dateTimezoneOffset property. The dateTimezoneOffset represents the difference in hours between the needed time zone and UTC.

For example, the GMT-5 time zone can be set like this:

report: {
  options: {
    dateTimezoneOffset: -5
  },
  dataSource: {
    // ...
  }
}

Live example

Set a time zone for "date string" and "datetime" fields

For "date string" and "datetime" fields, you can set a time zone right in the datePattern and dateTimePattern options.

To set a time zone, include "GMT+-N:" at the beginning of the pattern (e.g., "GMT+6:dd/MM/yyyy"). N should be changed from 1 to 12. Check out an example for the datePattern and the dateTimePattern.

Note Time zone set in the format patterns will override a time zone set for all fields.

For all other date types, set the dateTimezoneOffset option.

Set a time zone for specific date fields

To set a time zone for a specific "date string" or "datetime" field, include "GMT+-N:" at the beginning of the pattern set in the format mapping property. N should be changed from 1 to 12.

Time zone set in the format will override time zone configurations for all fields and for fields of a specific type.

Example:

report: {
  options: {
    dateTimezoneOffset: +5
  },
  dataSource: {
    data: [
      // ...
    ],
    mapping: {
      "OrderDateTime": {
        type: "datetime",
        format: "GMT+6:dd/MM/yyyy HH:mm:ss",
      }
    }
  }
}

Live example

In the example above, dateTimezoneOffset is ignored for the "OrderDateTime" field because the format property in the mapping overrides the time zone.

Rounding dates using the mapping.interval

Dates in Flexmonster can be rounded down with the mapping.interval property. Use this feature to group dates by a certain interval on the pivot table.

In the code snippet below, all dates have the same day part, but different time parts. The "1d" interval will round all dates to 2021-04-25T00:00:00:

report: {
  dataSource: {
    data: [{
        "DeliveryDate": "2021-04-25T21:30:05",
        "Price": 200
      },
      {
        "DeliveryDate": "2021-04-25T23:55:00",
        "Price": 150
      }
    ],
    mapping: {
      "Date": {
        interval: "1d",
        type: "datetime"
      }
    }
  }
}

Live example

Note For "json" and "csv" data source types, "date string" fields are rounded down with the "1d" interval by default.  If you want to display the actual time instead of 00:00:00, specify a smaller interval in the mapping (e.g., with the "1h" interval value, "04/25/2021T21:30:05" is rounded down to "04/25/2021T21:00:00").

Formatting time fields

In Flexmonster, time fields are formatted using pattern strings. Formatting can be applied to all time fields or to each field separately.

Format all time fields

To format all fields of the "time" type, use the options.timePattern property:

report: {
  options: {
    timePattern: "d'd' HH'h' mm'min'"
  },
  dataSource: {
    data: [
      // ...
    ],
    mapping: {
      "Time": {
        type: "time"
      }
    }
  }
}

Live example

Format specific time fields

To format a specific time field, use the format property in the mapping. The format overrides the options.timePattern property.

Here is an example of how to define formatting for a certain field:

report: {
  dataSource: {
    data: [
      // ...
    ],
    mapping: {
      "Duration": {
        type: "time",
        format: "d'd' HH'h' mm'min'",
      }
    }
  }
}

Live example

Supported time patterns

See the list of the supported patterns for "time" fields below:

Examples of time formats:

See also

• Configuring number formatting
• Applying conditional formatting
• Setting configurations for all reports
• Exporting data