Converting dates and timestamp fields in QuickSight

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”:

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)='th' OR
    substring({InputDate},3,2)='nd' OR
    substring({InputDate},3,2)='st' OR
    concat(substring({InputDate},1,2),substring({InputDate},5,strlen({InputDate})-4)), ifelse(
      substring({InputDate},2,2)='th' OR
      substring({InputDate},2,2)='nd' OR
      substring({InputDate},2,2)='st' OR
      concat(substring({InputDate},1,1),substring({InputDate},4,strlen({InputDate})-3)), NULL
DateParsed ifelse(
    parseDate({DateStripped},'dd MMM yyyy HH:mm:ss')=NULL,
    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,

@abacon - thanks very much for this. Date fields can be quite tricky…

I have a question on the use of epoch dates. I have a field in my upstream data which I believe is a Bitwise date - an example below:


Converted in exel : =INT((A1/(2^22)))/1000

Result - 1657281528, then converted to Epoch is 08/07/2022.

My data is stored in S3 and I will be moving it into Athena via Glue - Should I convert this to a human readable date along the way, or am I better off passing the epoch result (1657281528), then using the epochDate in a calculated field? If the latter, does this have speed of query impacts on the dashboard as it is having to constanctly calculate on the fly (my data is time series based)?


1 Like

Hi @QSCommUser,
If your requirement is for dashboards to be as performant as possible, then can I assume you are using SPICE? If so, SPICE encodes datetimes as 64 bit integers. As you identified, if you choose to store the epoch result instead, you would have to make an additional call to epochDate to convert it. This could add computational overhead to your dashboards. Therefore, I would ensure that datetimes emitted from Athena can be ingested by QuickSight directly into its native Date data type.
Are you needing to perform any calculations on the dates or are they simply being used as a dimension?