Date and timestamp fields can be represented in a seemingly endless number of formats. Many of these are automictically recognised by QuickSight, but if your date and timestamp fields use some of the more complex or non-standard formats you will need to convert them in QuickSight while preparing your dataset to a Date datatype (which can include either dates or dates plus times).
Using the supported date formats you can easily convert these timestamp “strings” into a supported datatype by using the Change data type option in the dataset editor:
This gives you the ability to specify the correct format and test it using the Validate option, for example:
The ability to specify the date format works well for many incoming dates, such as:
| Timestamp | Format |
|---|---|
| 280123 | ddMMyy |
| 28012023 | ddMMyyyy |
| 28-01-2023 | dd-MM-yyyy |
| 28/01/2023 | dd/MM/yyyy |
| 28.01.23 12:00 | dd.MM.yy hh:mm |
But some timestamps can prove more challenging, such as handling additional characters like commas, or T’s and Z’s as per the ISO 8601 date format:
| Timestamp | Format |
|---|---|
| 2023, Jan 28 12:01:23 | yyyy, MMM dd HH:mm:ss |
| 28-01-2023T12:00:00Z | dd-MM-yyyy'T'HH:mm:ss'Z' |
| 28-01-2023T12:00:00.000Z | dd-MM-yyyy'T'hh:mm:ss.SSS'Z' |
However, some timestamps are formatted in such a way that you cannot just edit the format in the QuickSight dataset, in certain situations you may need to invoke the parseDate() function in a calculated field to handle additional components of a timestamp, such as AM/PM:
| Timestamp | Calculated Field |
|---|---|
| 28/01/2023 06:00 AM | parseDate({InputDate},'dd/MM/yyyy hh:mm a') |
| 28/01/2023 06:00:00 PM | parseDate({InputDate},'dd/MM/yyyy hh:mm:ss a') |
Then, there are cases where unsupported components of a timestamp (those not supported in QuickSight by the Joda DateTimeFormat class) need to be handled. For example, take the following examples for ordinal day numbers, i.e. those containing “st”, “nd”, “rd” or “th”:
| Timestamp |
|---|
| 1st Jan 2023 12:00:00 |
| 2nd Jan 2023 12:00:00 |
| 3rd Jan 2023 12:00:00 |
| 14th Jan 2023 12:00:00 |
| 21st Jan 2023 12:00:00 |
The above timestamps can be converted by first removing the day number suffixes and then parsing the timestamp with parseDate(), I created 2 calculated fields to achieve this. The first, DateStripped, locates the suffix with substring() and then removes the suffix using concat() and substring() together. The second, DateParsed, then uses parseDate() on DateStripped to handle single or double-digit days in the timestamp:
| Calculated Field | Formula |
|---|---|
| DateStripped | ifelse(
substring({InputDate},3,2)='nd' OR substring({InputDate},3,2)='st' OR substring({InputDate},3,2)='rd', concat(substring({InputDate},1,2),substring({InputDate},5,strlen({InputDate})-4)), ifelse(
substring({InputDate},2,2)='nd' OR substring({InputDate},2,2)='st' OR substring({InputDate},2,2)='rd', concat(substring({InputDate},1,1),substring({InputDate},4,strlen({InputDate})-3)), NULL |
| DateParsed | ifelse(
parseDate({DateStripped},'d MMM yyyy HH:mm:ss'), parseDate({DateStripped},'dd MMM yyyy HH:mm:ss') |
Finally, some timestamps are stored as an epoch value, which is an integer representation of a date as the number of seconds since 00:00:00 UTC on January 1, 1970, to convert these, there is a dedicated function epochDate you can use in a calculated field:
| Timestamp | Calculated Field |
|---|---|
| 1675122072 | epochDate(InputDate) |
These are just some examples of how to handle incoming dates and convert them to Date datatypes in QuickSight.
Feel free to comment and/or add your own examples below to make this a living article that benefits the wider community.
Hope this helps,
Andrew
