MALFORMED_DATE on SPICE refresh with two different (valid) date formats

Question & Answer
,
1

Hello all!

I am regularly importing data from S3 into QuickSight (QS)/SPICE. The S3 data results from a DynamoDB to S3 export. One of the columns of the imported data contains timestamps and is typed in SPICE as “Date”. Up until recently, the timestamp format of the values in that columns was like this one “2024-06-03T09:52:43Z” (i.e. w/o milliseconds).

However, recently, by changes in the application code which produces the items in the DynamoDB table, the timestamp format has changed to “2024-06-03T09:52:43.722Z” (i.e. now millis are included). This leads SPICE to reject all items containing millis with an MALFORMED_DATE error.

I assume that basically, both formats (with and without milliseconds) are supported by QS/SPICE (my understanding of the corresponding documentation). However, if different supported date formats are used for values in the same column in parallel, this cannot be handled by QS. Is that correct?

If so, what do you recommend to solve this issue in order to get all items (regardless of whether the value for the date column is with or without milliseconds) imported successfully into QS?

Thanks,
Kaspar

2

Hi @Kaspar ,

Welcome back to the QuickSight Community!

Yes, the reason you identified is spot on. Since it is a mix of date formats QS is unable to interpret the dates. Below are the two ways I can think of solving this :

  • Edit the custom sql in the dataset to format the date field to date without mili seconds. That way we conserve single date format in the field. However, this should be done only when you do not have dependency to use the mili seconds information, since this would cause data granularity loss on the QS dataset level.
  • This option is effort intrinsic and functionally exhaustive. This should be done only if the mili seconds information is needed in the reports. Backfill existing table records with .SSS information defined as .000 to maintain single date format.

Hope this help!

Thanks,
Prantika

2 Likes
3

@prantika_sinha Many thanks for the confirmation and the suggested remedies. Since a millisecond-level of detail is not needed in the QS reporting, I will likely opt for the first option. :+1:

Thanks!