How do I resolve inconsistent date formatting issues in Amazon Quick?

Short description

Amazon Quick supports date formats based on the DateTimeFormat Joda patterns for file uploads and for data sources from Amazon Simple Storage Service (Amazon S3), Amazon Athena, and Salesforce. For more information on DateTimeFormat Joda patterns, see Class DateTimeformat on the Joda website.

Resolution

Date formatting issues in Amazon Quick might occur because of differences in how dates are handled across the UI display layer, export functionality, and calculated field functions.

Export your visual with formatted dates

Before you export your visual, make sure that you use the following formatDate function to convert the date to a string format:

formatDate({myDateField}, 'dd-MMM-yyyy')

Note: Replace myDateField with your date field.

Then, use the calculated field output in your visual instead of the raw date field.

Note: If you use the formatDate function, then you can't use date-specific features like date aggregation or date filters on this field.

If you export an analysis or dashboard to CSV or Excel without using the formatDate function, then the date formatting that you apply in the visual might not be saved in the exported file.

Resolve formatDate error

If you run the formatDate function with string data type or inconsistent case sensitivity, then you might receive the "Unrecognized format" error message. To resolve this error, take the following actions:

• Make sure that you use the correct case sensitivity in the format pattern.
  Use the yyyy format for a four-digit year.
  Use the MM format for a two-digit month.
  Use the dd format for a two-digit day.
  Use the HH format for hours.
  Use the mm format for minutes.
  Use the ss format for seconds.

• If your format string contains alpha characters like T, then enclose the entire format in double quotes or escape single quotes with a backslash.
  Example with double quotes:

  formatDate({myDateField}, "yyyyMMdd'T'HHmmss")

  Note: Replace myDateField with your date field.
  Example with single quotes and backslash:

  formatDate({myDateField}, 'yyyyMMdd\'T\'HHmmss')

  Note: Replace myDateField with your date field.

• Confirm that the input field has a Date data type. If the field has a String data type, then use the parseDate function to convert the data to a date.

Convert string columns to date columns in datasets

If your data source contains dates stored as strings, then Amazon Quick might not automatically recognize them as dates.

Important: You must convert integer dates that aren't Unix epoch datetimes to text format before you can convert them to date format.

To convert your string columns to date columns, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Data.
3. Select the dataset, and then choose Edit dataset.
4. Select the column.
5. For New Data type, choose Date.
6. For Edit date format, enter the format pattern that matches your source data.
7. Choose Validate.
8. Choose Apply.
9. Choose Save & publish.

During ingestion, Amazon Quick skips rows with values that don't match the specified format.

For CSV files with single-digit day or month values, make sure that you maintain formatting consistency in your source data.

Change the visual axis display date format

If you change the aggregation level of a date field on a visual axis, then Amazon Quick automatically adjusts the display format. For example, if you switch to monthly aggregation, then Amazon Quick might display yyyy-MM instead of the full date.

To control the display format, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Analyses.
3. Select the analysis.
4. Select the visual.
5. Select the column.
6. Choose Format.
7. Select your preferred date format, or enter a custom date format.

Add time component to date fields

If your date field shows only the date without the time component, then take the following actions:

• Make sure that the source data includes time information.
• To check the field format settings in the visual, select (right-click) the field, and then choose Format to include time.
• If the data source is PostgreSQL or similar, then verify that the column type is TIMESTAMP and not DATE.

Set default date formats and locale

Amazon Quick doesn't support a global default date format or locale at the account level. To resolve this issue, take one of the following actions:

• Customize the date display format. Use a formatDate calculated field with the pattern that matches your locale in the dataset or analysis.
• Customize the start of the week.

Check source data accuracy after dataset refresh

If your date values appear as null or incorrect after a dataset refresh, then verify that the source data format hasn't changed. Also, make sure that the date format specified in the dataset configuration still matches the source data.

To check for not valid date values that are replaced with null during ingestion, complete the following steps:

1. Open the Amazon Quick console.
2. In the navigation pane, choose Data.
3. Edit the dataset and filter for null values to see which rows lost their dates.

For SPICE datasets, perform a full refresh.