Modifying date and time format in dashboards

As a tenant administrator or editor, BMC Helix Dashboards provides you with a range of options for modifying the date and time format. 

Related topic

Configuring-dashboards-with-additional-customizations

Supported-database-functions

Scenario

You are a reporting administrator at Apex Global, and as a part of your regular work, you generate reports and share them with different stakeholders. Now let's say, your manager wants a report for all the critical incidents submitted for a specific time range and in a particular time format.

You can add an override by using the Fields with name option to supersede the default settings. This override generates the report as required and displays the information requested by your manager.

The following video explains how to configure an override for the date and time format:

https://www.youtube.com/watch?v=pBQ-2OZtuXU

To modify the date and time format

  1. While creating a new dashboard panel, click Add field override below the Override section on the navigation pane.
  2. Click Fields with name and select the column name that displays the date and time.
  3. Click Add override property and select Standard options > unit.
  4. Click Date & time and select one of the following options to display the date and time:
    • Datetime ISO
    • Datetime ISO (No date if today)
    • Datetime US
    • Datetime US (No date if today)
    • Datetime local
    • Datetime local (No date if today)
    • Datetime default
    • From Now
  5. Click Save.

Alternatively, you can configure a default format for the date and time at tenant level, which is applicable for all dashboards and panels. For more information, see To configure the default date and time format.

Best practice
BMC recommends setting the Last 90 days as the default value of the time range to avoid fetching large amounts of data from the database. The time range can be changed to a customized range after the dashboard loads.


To set custom date and time format

  1. While creating a new dashboard panel, click Add field override below the Override section on the navigation pane.
  2. Click Fields with name and select the column name that displays the date and time.
  3. Click Add override property and select Standard options > unit.
  4. Specify the custom date and time format in the Standard options > unit field.
    For example, you can specify the date and time in the following format:
    time:DD/MM/YYYY HH:mm:ss
  5. Click Save.

Examples

  • Specify the date and time in time:DD/MM/YYYY HH:mm:ss format to display the date and time as 21/04/2022 18:22:03.
  • Specify the date and time in time:Do of MMMM, Lt, YYYY (dddd) format to display the date and time as 19th of May, 05/19/2022t, 2022 (Thursday).
  • Specify the date and time in time:DD/MMM/YYYY (ddd) hh:mm:ss A format to display the date and time as 21/Mar/2022 (Mon) 06:22:03 PM.


To calculate the difference between dates and timestamps in dashboards

If your dashboard displays both the start and end dates, you can calculate the difference between the two dates and timestamps. This information will determine the amount of time needed to resolve an incident or ticket.

Let's say you are a tenant administrator who wants to calculate the difference between incident submit date and incident closed date for the most recent one month. You can use any one of the following methods to calculate the difference:

To calculate the difference between dates and timestamps by using the binary operation

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Select Transform > Add field from calculation and do the following actions:
    • In the Mode field, select Binary operation.
    • In the Operation fields, select the names of the fields on which you want to perform the calculation along with the subtraction symbol.

    • (Optional) In the Alias field, enter the name of your new field.
  2. On the navigation pane, click Add field override. 
  3. Click Fields with name and select the column name that you created in the Alias field.
  4. Click Add override property and select Standard options > unit.
  5. Select Time > milliseconds (ms).
    BMC Helix Dashboards will automatically calculate the difference in days, months, and years based on the data available.

To calculate the difference between dates and timestamps by using the SQL query

  1. Open the dashboard that you want to edit.
  2. Click the title bar of the panel that displays both start and end dates, and click Edit.
  3. Add the following query in the SQL Editor to calculate the difference between the submit date and the current date:
    DATEDIFF('second',`HPD:Help Desk`.`Submit Date`,currentDate()) AS Differencewhere second is the unit of calculating the difference between date and timestamp and Difference is the name of the new field. You can specify different date ranges in the SQL query according to your business requirements.
  4. On the navigation pane, click Add field override. 
  5. Click Fields with name and select the column name that you specified in the SQL query.
    In this case, we created a field called Difference.
  6. Click Add override property and select Standard options > unit.
  7. Select Time > seconds (s).
    BMC Helix Dashboards will automatically calculate the difference in days, months, and years based on the data available.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*