Understanding date and time formats
An event log has three important fields: Case ID, Activity, and Timestamp.
Of these, Timestamp is the one that probably requires the most attention. This page describes the issues you need to consider.
Timestamps are always in the DATETIME format
Quickstarts automatically detects the format of each column in your event log. In most cases it interprets the data correctly but you can override the suggestions it makes if you need to.
In this example, Quickstarts thinks that col_2 is a STRING. You would need to change it to DATETIME.
Tip
When this happens, it's likely that Quickstarts is confused and you might need to adjust the format of the Timestamp.
Adjusting the format of a timestamp column
Dates in CSV data can take a wide range of formats. To load CSV data using Quickstarts, you'll need to indicate the format that this particular CSV file is using.
For example, these dates are all in different formats:
23.12.2022
23/12/2022
12.23.2022
12/23/2022
Quickstarts will suggest the format it thinks is correct but you can edit the format if you want to.
To edit the Timestamp format:
Click the pen icon next to the Timestamp column header.
Manually edit the format.
Click the pen icon again to close the Timestamp format editor.
Things to keep in mind:
Use the same separators in your formatting string that you see in the data. If the time appears in the CSV as 19:45;01, the format is HH:mm;ss.
"MM" represents months in a two-digit format.
"mm" represents minutes in a two-digit format.
"hh" represents hours in a 12-hour format.
"HH" represents hours in a 24-hours format.
Don't enclose Timestamps in single or double quotes.
Example of editing the timestamp format
1. If my data looks like this, what should I set the format to?
25/12/2020 19:19:19
The appropriate format is: dd/MM/yyyy HH:mm:ss
2. If my data looks like this, what should I set the format to?
12.25.2020 19-19-19
The appropriate format is: MM.dd.yyyy HH-mm-ss
Important
Of course, it's a good idea to do some testing too. For example, that 19:19:19 might not be what you think, so we'd advise you to look at other "rows" in your data to make sure you understand the nature of the data. For example, if the next "row" of data shows a time such as 19:59:19, you might conclude that the time format is in an unusual format that requires more careful attention.
The bottom line here is that data can be messy and inconvenient sometimes, and difficult to interpret. As long as it's consistent, the techniques described on this page will be sufficient.
Tidying the data
Sometimes a CSV file needs a little manual cleanup. For example, this CSV, exported from a spreadsheet, has two problems:
sample_event_log (1),,,,
Flight,Activity,Timestamp,City,
TR320,Check-in,01/12/2019 12:01:42,Munich,
TR320,Boarding,01/12/2019 12:45:00,Munich,
TR320,Take off,01/12/2019 13:03:00,Munich,
TR320,Landing,01/12/2019 22:05:00,New York,
First, the trailing comma at the end of each line is confusing. Quickstarts sees the comma and concludes that there are five columns rather that four. You would need to delete those commas.
Second, Quickstarts requires the first line to be the list of headers. In this example, the export to CSV has added one more row containing the name of the event log. The cleaned up version of this CSV looks like this:
Flight,Activity,Timestamp,City
TR320,Check-in,01/12/2019 12:01:42,Munich
TR320,Boarding,01/12/2019 12:45:00,Munich
TR320,Take off,01/12/2019 13:03:00,Munich
TR320,Landing,01/12/2019 22:05:00,New York
This event log loads correctly using this Timestamp format string: MM/dd/yyyy HH:mm:ss
Other formatting issues
There are some other formatting elements that you might occasionally encounter:
"zzzz" represents a timezone
"a" represents AM/PM