Adjust Timestamp (Expression Function)

Purpose

The Adjust Timestamp function (ADJUST_TIMESTAMP) adjusts a numeric Unix timestamp in the amount and units specified. While timestamps are just a count of seconds and adding and subtracting seconds can be used to adjust them it is recommended that the adjust timestamp function is used. When crossing certain thresholds such as day light saving, dst, time start or end simply adding 1 days worth of seconds will likely give an undesired results. If a timestamp normally represents 9AM to the user and 1 day of seconds is added and the dst threshold is crossed then the timestamp will now represent 8AM or 10AM to the user.

Parameters

The Adjust Timestamp function has three required parameters:

Timestamp

Allowed Inputs: field value or a number representing a Unix timestamp.

There are several sources of Unix timestamps available. For example the stored value of a date or date time field is a Unix timestamp. Also When using the advanced interface the output of the date or date time expression function is a Unix timestamp.

Offset

Allowed Inputs: field value or a number

The offset is either a positive or negative number that will be the quantity of the offset. Either use the use the Query Builder to choose a field that contains the offset or use type a value to enter a number. The number does not need to be a whole number decimal values are permitted.

Unit

Allowed Inputs: field value or 'year', 'month', 'day', 'hour', 'minute', or 'second'.

The third parameter of the Adjust Timestamp function is a string that represents the unit of the offset. Either use the query builder to choose a field that contains the value or use type a value to enter a string.

Output

The output of the Adjust Timestamp function is number that is the new Unix timestamp. When adjusting by months the day may be adjusted for the new timestamp to land in the new target month for example adjusting a timestamp that represents July 31 by -1 month will result in a timestamp that represents June 30th the reverse is true when going forward a timestamp that represents January 31 adjusted by +1 month will result in a timestamp that represents either Feb 28th or Feb 29th depending on the year of the target timestamp.

Example

ADJUST_TIMESTAMP('1305586861', '-1', 'day') adjusts the timestamp's day from the 16th to the 15th which results in the timestamp '1305500461'.

expression function - adjust timestamp.txt · Last modified: 2016/09/14 18:19 (external edit)
Copyright WorkXpress, 2024