Field date values were not supported

Question & Answer
1

Hi, anybody encountered this issue with MYSQL database date field format? Is there a specific fix as my refresh always failed.

2

Hello @ebernardinojr,

Can you give us a little more information about what version of DB are you trying? What is the field type on the source and what kind of error do you get when refreshing?

If you can post the DDL of the field on the table you are trying to refresh I can try to replicate and see if I can help you further.

Kind regards,
Andres.

1 Like
3

Hi Andres,

Thank you for responding immediately

Version MySQL 5.7.38, field type is DATE. It use to work until a row was inserted with possibly an invalid date format.

Below is the specific error, the dataset is can no longer be refresh
The SKIPPED ROWS
1 rows where BIRTHDATE field date values were not supported.

image

4

Hi,

Is it possible to fix the row with the incorrect date? Can you click the download error rows file to see what is the date that is failing?

I find it strange that the value is accepted on the field by MySQL but it fails when importing into QuickSight, let’s dig a little more deeper.

Kind regards,
Andres.

1 Like
5

Hi @ebernardinojr - Give some sample rows so that right format can be set at data set level.

Regards - Sanjeeb

1 Like
6

sharing the specific error

image

7

Hi @ebernardinojr - If you see the BIRTHDATE, it is not a date field. Please fix your data first. If there is an issue with data, QS is not able to parse it, its good it is throwing the error.

Regards - Sanjeeb

1 Like
8

the front end accepted a date value, it might be an issue on the mysql version

image

1 Like
9

Hi @ebernardinojr - Is it possible to see this data from mysql? Clearly the data is not in the date format. You have to fix this data issue before importing to QuickSight , if all fields are same format, you can use transform to a date format in QuickSight as well.

Regards - Sanjeeb

10

Hello,

That looks like an EPOCH, but even if that is stored in miliseconds and a birthdate that looks like it is still a very very old person.

Probably the frontend is not validating a “reasonable” age and allowed someone to enter an invalid age.

I made some tests on my database and was able to reproduce the rows with “very old” dates to fail to be loaded into SPICE.

Looking at the documentation

For datasets stored in memory (SPICE), Amazon QuickSight supports dates in the following range: Jan 1, 1400 00:00:00 UTC through Dec 31, 9999, 23:59:59 UTC .

Where the date that is failing for you falls outside the allowed range for SPICE.

Hope this helps!

Kind regards,
Andres.

11

thanks, this is really helpful

12

thanks, we adjusted the data directly

1 Like