I use it to visualize my dynamoDB data with Athena Connector. I have LatestDate field which is initially a string and 99.99% of rows have format yyyy-MM-dd'T'HH:mm:ss.SSSSSS+SS:SS. And sometimes there are rows that have yyyy-MM-dd'T'HH:mm:ss+SS:SS format.
I don’t mind skipping them, so I just changed the data type for LatestDate to Date with yyyy-MM-dd'T'HH:mm:ss.SSSSSS+SS:SS. And as far as I understand Quicksight will drop invalid rows.
But then when I try to use this field in visualization it says
Amazon QuickSight can’t parse the data because it contains invalid dates or invalid numbers. Make sure your data contains only supported number and date formats.
P.S. I also tried different solution:
Created a new calculation in dataset where I transformed dates with yyyy-MM-dd'T'HH:mm:ss+SS:SS into yyyy-MM-dd'T'HH:mm:ss.SSSSSS+SS:SS and kept all rows. But when I try to use a new field - it throws the same error.
Since you are using Athena connector, I suggest you try switching to custom SQL and making the conversion via Athena (since you can easily handle both formats there). Use a query like the below:
SELECT
*,
COALESCE(
TRY(PARSE_DATETIME(date_field, 'YYYY-MM-dd''T''HH:mm:ss.SSSSSSZ')),
PARSE_DATETIME(date_field, 'YYYY-MM-dd''T''HH:mm:ssZ')
) as converted_date_field
FROM
tablename
where date_field is your current string date field. The query above will try to convert the field using both formats you specified and give an error if that fails. If you want the field to have value null (instead of giving error) if the value does not match any of those formats, then you can use:
SELECT
*,
COALESCE(
TRY(PARSE_DATETIME(date_field, 'YYYY-MM-dd''T''HH:mm:ss.SSSSSSZ')),
TRY(PARSE_DATETIME(date_field, 'YYYY-MM-dd''T''HH:mm:ssZ'))
) as converted_date_field
FROM
tablename
I tried using custom SQL (second option), but it raises a different issue.
Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.
It works flawlessly in Athena. But then when Quicksight does the query, it parses date_field as String. So I change its type to Date and it again raises an error during visualisation. This might be a different issue I suppose.
@Arman Since the query works in Athena, I suspect that QuickSight is not using the database where your table resides by default. Can you try changing the FROM part as follows:
FROM
database_name.tablename
(this query should still work in Athena)
If that does not work, this might be some limitation with QuickSight custom sql and Athena federation… In that case, you may try to add the query above as a view in Athena and then selecting that view as your table (without using custom sql).
Note that the query I gave you above adds a new field called converted_date_field whose type should be a timestamp. So you should not need to do any further conversion on this field. You can ignore your original date_field in QuickSight.
It shouldn’t be a problem, my current query looks like this:
select *,
COALESCE(
TRY(PARSE_DATETIME(LatestDate, 'YYYY-MM-dd''T''HH:mm:ss.SSSSSSZ')),
TRY(PARSE_DATETIME(LatestDate, 'YYYY-MM-dd''T''HH:mm:ssZ'))
) as ConvertedLatestDate
FROM "database"."default"."table"
Will try using a view.
On the Note, I was also expecting it to be a timestamp, but it’s not (see screenshot). And to do Date filtering in Quicksight, it should be converted to Date.
Hey @Arman
Actually the issue may be due to the timezone being included in the date/time field causing QuickSight to interpret it as String. Try casting the final result to timestamp as follows:
select *,
CAST(
COALESCE(
TRY(PARSE_DATETIME(LatestDate, 'YYYY-MM-dd''T''HH:mm:ss.SSSSSSZ')),
TRY(PARSE_DATETIME(LatestDate, 'YYYY-MM-dd''T''HH:mm:ssZ'))
) AS timestamp
) AS ConvertedLatestDate
FROM "database"."default"."table"
That should present the field with Date type to QuickSight so you will not need to convert it yourself.
I just 20 minutes ago in a similar manner converted everything to UNIX time, Quicksight parsed it as int and I had no problems later converting it to Date. But apparently your option is even easier, and Quicksight parsed it as Date!
