Add a local timestamp

The default date and time for a report is Greenwich Mean Time (GMT). To display the local date and time that a report was run, you can create a calculation using the TIMESTAMPADD function.

Complete the following steps to display the local date and time in a report.

  1. Add a local date-time calculation column.
  2. Display the local date-time in the report.

Subscription Interactive Custom Report Writer

Platform Services

Regional availability

All regions
User type Interactive Custom Report Writer: Business, Project Manager
Permissions

Interactive Custom Report Writer: Run, List, View, Add, Edit, Delete

Platform Services: Run, List, View, Add, Edit, Delete

TIMESTAMPADD function

The TIMESTAMPADD function allows you to create a calculation that adds or subtracts a specified number of intervals to return the desired date and time.

TIMESTAMPADD(interval, integer, timestamp)

  • Interval: Uses any of the following: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.
  • Integer: Uses either a positive or negative integer value.
  • Timestamp: Uses a valid Calendar/Date function. If the interval is SECOND, MINUTE, or HOUR, use a timestamp such as CURRENT_TIMESTAMP. For DAY, MONTH, QUARTER, or YEAR, you can use a timestamp or a plain date can be used.
The start and end Daylight Saving Time can be on different days in different countries. For this reason, a report might show the accurate date and time one day and be inaccurate the next, based on Daylight Saving Time differences.

Step 1. Add a local date-time calculation column

  1. Open ICRW and select Edit for the report to which you want to add a local timestamp.
  2. From the Refine tab, select the Calculation column icon.
  3. Enter a Folder title and Column title in the appropriate fields.
    The Folder title can be anything you like. The Column title appears in the report heading, so we entered Run date and time: in our example
  4. Enter the following TIMESTAMPADD calculation in the Calculation field, adjusting the HOUR interval to reflect your local timezone. The default time is GMT.

    TIMESTAMPADD(SQL_TSI_HOUR, -8, CURRENT_TIMESTAMP)

    To display Pacific Standard Time, 8 hours was subtracted (-8). To display Eastern Standard Time, you would subtract 5 hours (-5), and so on.
  5. Select OK.
    AP bill report with the local data and time shown in the Run date and time column.

Step 2. Display the local date-time in the report

  1. Right-click the date and time calculation column and select Move Column > To Sections.
  2. In the Table heading, select the Edit (pencil) icon.
  3. Go to Sections and if the Totals icon shows a check mark, select the icon and choose None from the menu.

    The Totals menu has the following options: None, Before, After, Format Labels, and Format Values.

  4. Select the Section Properties icon.
    Section Properties icon is highlighted at the top of the Sections pane, to make it easy to find.
  5. In the Section Properties dialog, select the Start radio button and select OK.
    The Start button is highlighted under Column Heading Display in the Section Properties dialog.
    This option displays the column heading before the date and time.
  6. On the Refine tab, select Done.
    The local date and time is shown in the heading for the report Run date and time.