Wrong format type for date fields: all values are mapped to January and timestamp is added

Question & Answer
,
1

Hello everyone,
I have a problem with date fields. All values are mapped into the same month (January) even if they span all 12 months.

Let me describe the situation.
I have a .csv data source stored in S3.
The .csv file contains several fields with dates, in the format “dd/mm/yyyy”.
When I create a dataset, QuickSight correctly reads the datasource and identifies the date fields as a date. However, for some reason, all entries are projected into January and added a timestamp.
The data source contains data for 4 different years (from 2018 to 2021) and for all 12 months within each of those years (there are some special cases with 1919/01/01).

In the following picture you can see the source data in the original format and what happens after I validate it with its format “dd/mm/yyyy”:

  • the timestamp is added
  • all months are january
    date_format_2

I have also create a timeseries in the analysis, so that I could see all of the data (vs a sample of 5 in the validate window), and it shows datapints in January only for the whole period.

Any idea of why this is happening?
And how I could get the correct values for all the dates?

Thanks!

2

After several attempts, I solved the problem.
Here is the solution in case others might have the same issue.

The solution is using capital “M” for the months, while I was using lowercase “m”.

As you can see in the image below, using “dd/MM/yyyy” lets QuickSight map the months properly and the data is recnogised correctly.
I still do not understand why the timestamp appears, however it does little harm.
date_format_4