Expression Functions for Timestamp Manipulation
The following functions are designed for timestamp conversions and are provided primarily for use with the Sql Variables driver.
It is recommended to enclose text values with quotation marks, and it is mandatory for the expression to work as expected if the value contains a special character such as a space, a comma or a quotation mark. For example: StrRemove('ab,cdefgh', 3, 2).
Common parameters
| Parameter | Description |
| Kind |
Timestamp kind, use either predefined constant TS_Utc or TS_Local. |
| Format |
Type: String. Timestamp format string. Year: YY or YYYY. Month: MM. Day: DD. Hours: HH. Minutes: mm. Seconds: ss. Milliseconds: fff, ff or f. Z; Indicates if the time is UTC or Local. #: Allows some of the timestamp parts to be ignored. |
| TS |
Type: Double. A timestamp expressed as the number of milliseconds since the first of January 1970 at 00:00:00.000, as used by the SCADA Basic instruction DATETIMEVALUE. |
Functions
| Function and syntax | Operation |
| StrToTs(Str, Kind, Format) |
Returns the timestamp Str as a double. Example 1: StrToTS('2020-04-14T17:06:26.423Z', TS_Utc, 'YYYY-MM-DDTHH:mm:ss.fffZ') > 1586883986423 Example 2: StrToTS('2020-04-14T17:06:26.423Z', TS_Local, 'YYYY-MM-DDT##:##:##.###Z') = 1586876786423 Ignores the time and takes date only. |
| StrToFileTimeUtc(Str) | Returns the timestamp Str as a UTC FileTime. Example: StrToFiletimeUtc('2019-10-29T16:14:40.284Z') = 132168392802840000 |
| TSToStr(TS, Kind [, Format]) |
Returns the timestamp TS as a string. If Format is omitted then a default format of YYYY-MM-DDTHH:mm:ss.fffZ is used. In addition to the usual format characters: If Format is 'DATE' then YYYY-MM-DD is used. If Format is 'TIME' then HH:mm:ss.fffZ is used. Example 1: TsToStr(1586883986789, TS_Utc) = '2020-04-14T17:06:26.789Z' Example 2: TsToStr(1586883986789, TS_Utc, 'Date') = '2020-04-14' |
| TSToOADate(TS, Kind) |
Returns an OLE Automation date from the TS. Example: TSToOADate(1586883986423, TS_Utc) = 637224807864230000 |
| OADateToTS(Date, Kind) |
Returns a TS from an OLE Automation date. Date is a double. Example: OADateToTS(637224807864230000, TS_Utc) = 1586883986423 |
| TSToFileTime(TS) |
Returns a FileTime date from a TS. Example: TSToFileTime(1586883986423) = 132313575864230000 |
| FileTimeToTS(FileTime) |
Returns a TS from a FileTime date. Example: FileTimeToTS(132313575864230000) = 1586883986423 |
| TSToPosixTime(TS) | Returns a Posix time from a TS. |
| PosixTimeToTS(PosixTime) | Returns a TS from a Posix time. |
| TsGetYears(TS, Kind) |
Returns the year of a TS. Example: TSGetYears(1586883986423, TS_Utc) = 2020 |
| TsGetMonths(TS, Kind) |
Returns the month of a TS. Example: TSGetMonths(1586883986423, TS_Utc) = 4 |
| TsGetDays(TS, Kind) |
Returns the days of a TS. Example: TSGetDays(1586883986423, TS_Utc) = 14 |
| TsGetHours(TS, Kind) |
Returns the hours of a TS. Example: TSGetHours(1586883986423, TS_Utc) = 17 |
| TsGetMinutes(TS, Kind) |
Returns the minutes of a TS. Example: TSGetMinutes(1586883986423, TS_Utc) = 6 |
| TsGetSeconds(TS, Kind) |
Returns the seconds of a TS. Example: TSGetSeconds(1586883986423, TS_Utc) = 26 |
| TsGetMilliseconds(TS, Kind) |
Returns the milliseconds of a TS. Example: TSGetMilliseconds(1586883986423, TS_Utc) = 423 |
| TsAddYears(TS, Kind, Number) |
Returns a TS with an added Number of years. Number can be positive or negative. Example: TSAddYears(1586883986423, TS_Utc, 2) = 1649955986423 |
| TsAddMonths(TS, Kind, Number) |
Returns a TS with an added Number of months. Number can be positive or negative. Example: TSAddMonths(1586883986423, TS_Utc, 2) = 1592154386423 |
| TsAddDays(TS, Kind, Number) |
Returns a TS with an added Number of days. Number can be positive or negative. Example: TSAddDays(1586883986423, TS_Utc, 2) = 1587056786423 |
| TsAddHours(TS, Kind, Number) |
Returns a TS with an added Number of hours. Number can be positive or negative. Example: TSAddHours(1586883986423, TS_Utc, 2) = 1586891186423 |
| TsAddMinutes(TS, Kind, Number) |
Returns a TS with an added Number of minutes. Number can be positive or negative. Example: TSAddMinutes(1586883986423, TS_Utc, 2) = 1586884106423 |
| TsAddSeconds(TS, Kind, Number) |
Returns a TS with an added Number of seconds. Number can be positive or negative. Example: TSAddSeconds(1586883986423, TS_Utc, 2) = 1586883988423 |
| TsAddMilliseconds(TS, Kind, Number) |
Returns a TS with an added Number of milliseconds. Number can be positive or negative. Example: TSAddMilliseconds(1586883986423, TS_Utc, 2) = 1586883986425 |
| TsUtcToLocal (TS) |
Returns a local timestamp from the supplied UTC timestamp. Example: TSUtcToLocal(1586883986423) = 1586891186423 |
| TsLocalToUtc (TS) |
Returns a UTC timestamp from the supplied local timestamp. Example: TSLocalToUtc(1586891186423) = 1586883986423 |
| TsNow (Kind) | Returns the timestamp at the time the expression is calculated. Kind can be either TS_Utc or TS_Local. Example: TsNow(TS_Utc) = 1586883986425 |