Handling Dates, Date-times, and Timestamps

When dates and date-times appear as strings in the data in a format that Etleap does not automatically recognize, it’s necessary to add a parsing step to get the desired data type in the destination table. The same goes for timestamps represented as the number of seconds or milliseconds since the epoch.

Convert String Column to Date or Date-Times

If dates were not parsed automatically and appear as strings, you still can convert them manually.

Steps

1. Click on the “+Add script step” in the script editor.
2. Type in “date.” That will show you a few results.
3. Pick one of them depending on whether you have dates date-times.
4. In the column field, select the date column you want to convert. You can select multiple columns at the same time.
5. Before filling in the format field, use the i icon to see formatting examples.
6. Type in the format you want to use in the column, for instance, M/d/yyyy for strings that look like “5/6/1986”.
7. Optionally, add a where clause. This may be necessary if your date format varies based on the value in one or a few other columns.
8. Hit “Add”.

Convert Number Timestamp Column to Date-Times

Steps

If you have timestamp data in seconds or milliseconds, you can use “Interpret timestamp as timestamp in seconds” transform to parse and display them in the Date-Time format.

Shortcut: Click on the column header that contains the timestamp data. Review the suggested transform steps and select the one that matches. If none of the suggestions match, use the steps below.

1. Click on the “+Add script step” in the script editor.
2. Type in “timestamp.” That will show you a few results.
3. Depending on whether you have your timestamps in seconds or milliseconds, pick up one of the transforms.
4. Select the column to parse.
5. Optionally, add a where clause.
6. Hit “Add”.

Troubleshooting Timestamp Conversion

Wrong Year Parsed

If you see the wrong year (1970), as in the screenshot below, you have picked the wrong parser transform: seconds instead of milliseconds or the other way around. Use the dropdown menu to change it.