Concepts → Date System Variable

About a date system variable

A date system variable references date or time information. Incorta offers numerous built-in data system variables. A date system variable is available to all users and all services. The Formula Builder gives you access to date system variables when building formula expressions. Incorta calculates a date system variable when invoked.

Invocation of a date system variable

Incorta is an Apache Tomcat web application that runs in a Java Virtual Machine (JVM) on a host machine. By default, the JVM utilizes the host machine’s configurations for date and time.

Because a host machine may exist in another time zone, there is typically a disparity in time between a web browser on a client machine and the JVM running Incorta on the host machine.

When a date system variable is used, Incorta will base the time/date information on the host machine’s designated time zone, not the user. Incorta users should be aware of this fact when creating time sensitive formulas and filters.

Note

See Changing the JVM Time Zone to change the host machine time zone.

Date system variable in a physical schema

If you create a formula column in a physical schema that utilizes a date system variable, the variable is interpreted at load time and stored in memory.

Date system variable in a business schema or dashboard

If you use a date system variable in a business schema or dashboard, the variable is calculated at the time of query.

List of Date System Variables


The following are the available date system variables within Incorta:

Variable Data Type Description Example
$currentDate date The current date 10/3/20
$currentDay integer The current day; displayed as day of month 3
$currentMonth integer The current month 10
$currentMonthEnd date The last date of the current month 10/31/20
$currentMonthStart date The first date of the current month 10/1/20
$currentQuarter integer The numerical value of the current quarter 4
$currentQuarterStart date The first date of the current quarter 10/1/20
$currentTime timestamp Timestamp of current date and time 10/7/20, 8:12:14 PM
$currentWeek integer The numerical value of the current week of the year 40
$currentWeekStart date The date of the first day of the current week. 9/27/20
$currentYear integer The current year 2020
$currentYearStart date The first date of the current year 1/1/20
$dayAgo date The date of the previous day 10/2/20
$lastMonth integer The numerical value of the previous month 9
$lastMonthStart date The first date of the previous month 9/1/20
$lastQuarter integer The numerical value of the previous quarter 3
$lastQuarterStart date The first date of the previous quarter 7/1/20
$lastWeek integer The numerical value of the previous week of the year 39
$lastWeekStart date The first date of the previous week 9/20/20
$lastYear integer The previous year 2019
$lastYearStart date The first date of the previous year 1/1/19
$monthAgo date The date one month ago 9/3/20
$nextMonthStart date The first date of the next month 11/1/20
$quarterAgo date The date one quarter ago 7/3/20
$weekAgo date The date one week ago 9/26/20
$yearAgo date The date one year ago 10/3/19

Date system variable formatting

If you are using a date system variable as the output of a formula column, the format can be edited in the properties panel of the Analyzer. The data type of a date system variable will determine which format properties can be used.

Additional Considerations

You can change the time zone of the host JVM machine. This is an option if your Incorta users are in one time zone and the Incorta host machine is on a different time zone.

Warning

The following steps require command line changes to Incorta Nodes. If you are not comfortable with editing systems from command line contact Incorta support for assistance. Also, you will need to maintain this update between upgrades.

The following are the steps to change the JVM time zone:

  • Backup the file startService.sh located under <incorta_installation_path>/IncortaNode
  • Edit the startService.sh file by changing the following line:
    From:
export JAVA_OPTS="-Xms"$memorySize" -Xmx"$memorySize" -Dfile.encoding=UTF-8"
To:
export JAVA_OPTS="-Xms"$memorySize" -Xmx"$memorySize" -Dfile.encoding=UTF-8" -Duser.timezone=America/Los_Angeles
  • The above change needs to be completed in both the loader and analytics nodes.
  • Restart the Incorta services.
  • Complete a full load from staging.