Modifying date and time format in dashboards

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.

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 21/04/2022 18:22:03.
• 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.

1. • (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.