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.

Syntax

Like many functions in DataPrime, parseTimestamp supports two notations, function and method notation. These interchangeable forms allow flexibility in how you structure expressions.

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

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

Arguments

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 parseTimestamp('2023-04-05') as ts # Result 1: { "ts": 1680652800000000000 }

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

Parse a date in US format

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

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

Parse date and time with units

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

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 parseTimestamp('1680710853', 'timestamp_second') as ts # Result 4: { "ts": 1680710853000000000 }

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 parseTimestamp(ts, 'timestamp_second|%Y-%m-%d|iso8601')

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.

Previous parseInterval

Next parseToTimestamp

Theme

Light

light

dark