SPICE incremental refresh using epoch time?

Q&A
1

We are using Athena/DynamoDB as our data source and trying to configure incremental SPICE refreshes. I have an epoch timestamp for each record but no other date fields in my dataset. Is there a way an incremental refresh can use this field? Right now Quicksight is telling me I don’t have a valid date field. I use calculated fields to transform my dates for visualizations but I presume SPICE can’t use this as they care calculated after the fact.

2

I don’t think it can use that.

Can you convert that to field to a date field?

Something like in these examples.

3

Hi @bills

Did you try out the solution provided by @Max ? If it helped you resolve the question, please help the community by marking his answer as a “Solution.”

If we don’t hear back in the next 7 days, we will archive this question. Thanks!

4

The data is coming from AWS IoT so it looks like I’m limited to the functions for data transformation, Functions - AWS IoT Core.

5

I found another date field (w/out time) that wasn’t being populated properly because its name is “date” which is probably reserved. When I did a “SELECT AS new_field_name” it started populating correctly. Even though the date doesn’t have a time SPICE can still use it for a daily refresh. Thanks all.

6

Ingest the date field as a string (toString function) then on the Transformation workflow Change Data Type, convert the calculation to Date format

7

Update on this, the real error is on the calculated field name, if the date field used to anchor the incremental refresh has space in the name, it will error out. So the actual solution/work around is to remove any space in the calculated field name.