Parse date (parsedate)
Parses a string representing a date using a custom format and converts it into a timestamp, optionally specifying a time zone to see the date in one different than yours and a language locale to adapt it. This is the inverse of the Format date (formatdate) operation.
How does it work in the search window?
Select Create column in the search window toolbar, then select the Parse date operation. You need to specify at least two arguments:
|Argument||Data type||More information|
|String to be parsed (mandatory)||string|
|Date format (mandatory)||string|
Enter the format of the string in the first argument exactly as it appears. You must use the notation for valid format patterns described below.
It must coincide with the string to be parsed, otherwise you will get the value "null".
You need a valid string format the app can recognize so it returns meaningful results. You can use one of the following:
You can check the time zone codes and region names in the Configure Timezone window in Devo. Be aware that some of the codes coincide, as CST could mean Central Standard Time or China Standard Time. In that case, it would be advisable to introduce the time zone's full name. You can find them listed in the following link.
If you leave the field empty or introduce a value the app cannot recognize, it will automatically set the Time Zone argument as UTC so you will see the time in UTC.
If the month part of the string to be parsed is expressed in a language other than your own, you need to add the locale argument to specify which one. Remember that your own language is determined by your user preferences.
If the locale argument does not match the language of the string or if the locale argument is not added, it will return the value "null".
The accepted format for the locale argument corresponds to the ISO 639-1 codes, which consist of two-character codes ("en" for English, "es" for Spanish, "fr" for French or "de" for German). It must no be confused with the ISO 639-2 codes and the ISO 639-3 codes, which consist of three-character codes. Check the following link for the full list: ISO 639-1 codes.
The data type of the values in the new column is timestamp.
In the Date format argument, enter the exact format of the strings to be converted using the following patterns.
|Type||Valid format patterns||Example|
|Year||YY, YYYY||19, 2019|
|Month||M, MM, MMM, MMMM||7, 07, Jul, July|
|Day in month||D, DD||9, 09|
|Hour (0...23, 00...23)||H, HH||7, 07|
|Hour (1...12, 01...12)||h, hh||7, 07|
|Minute||m, mm||3, 03|
|Second||s, ss||8, 08|
|Weekyear (15, 2015)||GG, GGGG|
|Week of week year (1...53, 01...53)||W, WW||5, 05|
|Parse TimeZone offset (-0700, ...+0700)||ZZ|
If the string to be parsed contains literal text, enclose it inside square brackets  so it can be correctly isolated and omitted when transformed. In the following example you can see how the literal text and date format must be treated:
- String to be parsed → Event occurred on January 23 of 2020 at 10:38
- Date format → [Event occurred on] MMMM DD [of] YYYY [at] HH:mm
Let's transform the Timestamp field in the
demo.ecommerce.data table into timestamp format.
Click Create column and select the Parse date operation. Assign a name to the new column - let's call it simply parsedate. Now, add the following arguments:
- String to be parsed: select the timestamp column.
- Date format: enter the following pattern, which corresponds to the format the string to be parsed presents: DD/MMM/YYYY:HH:mm:ss +SSSS
Click Create column and you will see the following result:
This example uses values in a data table generated from this CSV file. If you want to try the example for yourself, download the file and upload it to your domain clicking Data upload in the navigation pane. Name the new table
my.upload.sample.data and select Current date as Date parsing type. Learn more about uploading data in Uploading log files.
After receiving the confirmation message, you can access the table from the Finder, selecting my → upload → sample → data. When you upload data from a file, all the information is included in a single column called message. To split the values into different columns, you can use the Split operation. Click Toggle Query Editor in the search window toolbar and paste the following LINQ query to save time:
select split(message, ";", 20) as Str_Timestamp
Let's say we want to transform the Str_Timestamp field, which has the months in Spanish, into Timestamp format.
Click Create column and select the Parse date operation. Assign a name to the new column - let's call it simply parsedate. Now, add the following arguments and click Create column:
- String to be parsed: select the Str_Timestamp column.
- Date format: enter the following pattern, which corresponds to the format the string to be parsed presents: DD-MMMM-YYYY
- Time zone: you can select any time zone, however, we chose CET because it is the one in which Spain is located.
- Locale: es, which corresponds to the Spanish locale.
How does it work in LINQ?
Use the operator
as... and add the operation syntax to create the new column. These are the valid formats of the operation:
parsedate(string_to_be_parsed, format_string, time_zone_string)
parsedate(string_to_be_parsed, format_string, time_zone_string, locale_string)
Copy the following LINQ script and use it to replicate the first example on the
from demo.ecommerce.data select parsedate(timestamp, "DD/MMM/YYYY:HH:mm:ss +SSSS") as parsedate
Copy the following LINQ script and use it to replicate the second example on the
from my.upload.sample.data select split(message, ";", 20) as Str_Timestamp select parsedate(Str_Timestamp, "DD-MMMM-YYYY", "CET", "es") as parsedate