parseTimestamp - Parse a date time string into a timestamp

Often, dates and times are provided in string format. In order to take full advantage of DataPrime's many time functions, this string must be parsed into a timestamp type.

NOTE: If the string provided can not be parsed (for example, if it doesn't match the expected format), then parseTimestamp will return null.

Syntax

parseTimestamp(string: string, format: string?, tz: string?): timestamp

Arguments

Name	Type	Required	Description
timestamp	string	true	The timestamp string to parse
format	string	false	The format of the timestamp. Defaults to `auto`
tz	string	false	Must be a valid Time Zone string. See Time Zone section to find out more.

Defining the date/time format

The format string is optional, and will default to 'auto' which means the DataPrime engine will attempt to match the datetime string to well known format.

Both custom formats and well known formats can be provided. For example, to parse 2023-04-05, the following command will work:

create parsed_ts from date_time_str.parseTimestamp('%Y-%m-%d')

Here are some other examples of formats that are commonly used:

'%Y-%m-%d' - parse date only, e.g. '2023-04-05'
'%F %H:%M:%S' - parse date and time, e.g. '2023-04-05 16:07:33'
'iso8601' - parse a timestamp in ISO 8601 format, e.g. '2023-04-05T16:07:33.123Z'
'timestamp_milli' - parse a timestamp in milliseconds (13 digits), e.g. '1680710853123'
'%m/%d/%Y|timestamp_second' - parse either a date or a timestamp in seconds, in that order

Defining the timezone

Time Zones in DataPrime are well known string values that represent known timezone values by country or hour offsets. For example:

Time zone offset in hours (e.g. '+01' or '-02')
Time zone offset in hours and minutes (e.g. '+0130' or '-0230')
Time zone offset in hours and minutes with separator (e.g. '+01:30' or '-02:30')
Time zone shorthand (e.g. 'UTC', 'GMT', 'EST', etc.)
Time zone identifier (e.g. 'Asia/Yerevan', 'Europe/Zurich', 'America/Winnipeg', etc.)

To find out more, check out TimeZone information.

Example - Basic usage examples

The following example will parse a given date using the default format.

limit 1 | choose '2023-04-05'.parseTimestamp() as ts # Result 1: { "ts": 1680652800000000000 }

Parse a date in US format

limit 1 | choose '04/05/23'.parseTimestamp('%D') as ts # Result 2: { "ts": 1680652800000000000 }

Parse date and time with units

limit 1 | choose '2023-04-05 16h07m'.parseTimestamp('%F %Hh%Mm') as ts # Result 3: { "ts": 1680710820000000000 }

Parse a timestamp in seconds (10 digits)

limit 1 | choose '1680710853'.parseTimestamp('timestamp_second') as ts # Result 4: { "ts": 1680710853000000000 }

Example - Parsing inconsistent date-time values

It is common for multiple systems to produce date-time values in different formats. Consider the following documents:

{
    "ts": '1680710853123',
    "app": "app1"
},
{
    "ts": '2023-04-05',
    "app": "app2"
},
{
    "ts": '2023-04-05T16:07:33.123Z',
    "app": "app3"
}

In order to parse all of these values in a single command, we can use the | keyword to provide multiple formats:

create ts_parsed from ts.parseTimestamp('timestamp_second|%Y-%m-%d|iso8601')

This will result in a new field, ts_parsed with each parse value in timestamp format.

parseInterval

Next parseToTimestamp