Page Comparison

Table of Contents

maxLevel	2
type	flat

...

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.

Note
It must coincide with the string to be parsed, otherwise you will get the value "null".

Time zone

string

You need a valid string format the app can recognize so it returns meaningful results. If you leave the field empty or introduce a value the app cannot recognize, the default Time Zone is UTC. You can use one of the following methods:

A time zone code→ UTC, PST, CST, etc. Check the full list here.
The time zone database name → America/Los_Angeles, Europe/Amsterdam, Asia/Tokyo, etc. For the full list, see here.
The full name of the time zone → Universal Time Coordinated, Pacific Standard Time, Central Standard Time, etc. Check the full list here.
The name of the region as it appears in Devo → Asia/Shanghai, Europe/Athens, America/New York, etc. You can check the time zone codes and region names in the Configure Timezone window in Devo.

Note
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 avoid using codes and introduce any of the other formats mentioned.

Locale

string

Applies the correct language to the date based on the locale you specify. If this argument is not added, the date will be in your own locale, which is determined by your user preferences.

The accepted formats for the locale argument correspond to the ISO 639-1 codes, which consist of two-character codes ("en" for English, "es" for Spanish, "fr" for Frech or "de" for German), or a combination of this with the ISO 3166-2, which consists of two-character codes for specific countries ("gb" for United Kingdom, "es" for Spain...) and together they result in a four-character code specifying the subdivision ("en-us" for American English, etc.).

It must not be confused with the ISO codes that consist of three-character codes.

Note
As not all codes are recognized as valid locales, entering an invalid code will return the locale set in your user preferences. Check the full list of locales here.

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
AM/PM	A
Minute	m, mm	3, 03
Second	s, ss	8, 08
Millisecond (000...999)	SSS	056
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

Example 1

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:

...

Rw ui textbox macro

type	info

This example uses values in a data table generated using the CSV file below:

Ui button

tooltip	Download this CSV file to try the example yourself
title	Download CSV file here
url	https://drive.google.com/uc?export=download&id=1OfvPMy0PoTnlMWVJEwhjJE3TjjslJTGl

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:

from my.upload.sample.data
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:

...

Use the operator select... 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)
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 demo.ecommerce.data table.

Code Block
from demo.ecommerce.data select parsedate(timestamp, "DD/MMM/YYYY:HH:mm:ss +SSSS") as parsedate

...

Versions Compared

Old Version 14

New Version 15

Key

How does it work in the search window?

Example 1