how to edit dd.mm.yyyy dateformat in apache superset-ui-chart-controls

[mujohiddin]

### Bug description

26 countries use "dd.mm.yyyy" as date format. (https://en.wikipedia.org/wiki/Date_format_by_country)

I'd like to add this format ("%d.%m.%Y "in D3 notation) below "Adaptive Formatting" in but not working

How to reproduce the bug

how to edit datetime

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• [ ] I have searched Superset docs and Slack and didn't find a solution to my problem.
• [ ] I have searched the GitHub issue tracker and didn't find a similar bug report.
• [ ] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot[bot]]

Hallo @mujohiddin! Schön, dich wieder hier zu sehen. Ich schaue mir dein Problem an und werde dir in Kürze eine ausführliche Antwort geben.

[maximStafeevAtAllscripts]

Hi! I have the same issue. I need to show all my timestamps in %Y-%m-%d %H:%M:%S.%L format with thousands of second but cannot change format in UI. I updated chart via API with this format and it works, but I wand to have it in UI also. I found all this array of D3 format options in D3Formatting.ts. It seems it is hardcoded.

[dosubot[bot]]

To add or modify the timestamp format options in the Apache Superset UI to include %d.%m.%Y and %Y-%m-%d %H:%M:%S.%L, you need to update the D3_TIME_FORMAT_OPTIONS array in the controls.jsx file.

Here is how you can do it:

1. Locate the controls.jsx file in your Superset codebase.
2. Update the D3_TIME_FORMAT_OPTIONS array to include the new formats.

Here is an example of how to add these formats:

export const D3_TIME_FORMAT_OPTIONS = [
  ['smart_date', t('Adaptive formatting')],
  ['%d/%m/%Y', '%d/%m/%Y | 14/01/2019'],
  ['%m/%d/%Y', '%m/%d/%Y | 01/14/2019'],
  ['%Y-%m-%d', '%Y-%m-%d | 2019-01-14'],
  ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S | 2019-01-14 01:32:10'],
  ['%d-%m-%Y %H:%M:%S', '%Y-%m-%d %H:%M:%S | 14-01-2019 01:32:10'],
  ['%H:%M:%S', '%H:%M:%S | 01:32:10'],
  ['%Y-%m-%d %H:%M:%S.%L', '%Y-%m-%d %H:%M:%S.%L | 2019-01-14 01:32:10.123'], // New format
  ['%d.%m.%Y', '%d.%m.%Y | 14.01.2019'], // New format
];

This will ensure that the %d.%m.%Y and %Y-%m-%d %H:%M:%S.%L formats are available and correctly used in the Apache Superset UI ^[1].

Additionally, ensure that these formats are referenced in other parts of the codebase where D3_TIME_FORMAT_OPTIONS is used, such as in sharedControls.tsx and constants.tsx:

• In sharedControls.tsx:

const x_axis_time_format: SharedControlConfig<
  'SelectControl',
  SelectDefaultOption
> = {
  type: 'SelectControl',
  freeForm: true,
  label: t('Time format'),
  renderTrigger: true,
  default: DEFAULT_TIME_FORMAT,
  choices: D3_TIME_FORMAT_OPTIONS,
  description: D3_TIME_FORMAT_DOCS,
  filterOption: ({ data: option }, search) =>
    option.label.includes(search) || option.value.includes(search),
};

• In constants.tsx:

const d3TimeFormat: ControlFormItemSpec<'Select'> = {
  controlType: 'Select',
  label: t('D3 format'),
  description: D3_TIME_FORMAT_DOCS,
  options: D3_TIME_FORMAT_OPTIONS.map(option => ({
    value: option[0],
    label: option[1],
  })),
  defaultValue: D3_TIME_FORMAT_OPTIONS[0][0],
  creatable: true,
  minWidth: '10em',
  debounceDelay: 500,
};

By updating these files, you ensure that the new date formats are available throughout the Superset UI ^[2][3].

If you want to verify the functionality of these new date formats, you can add test cases in the createD3TimeFormatter.test.ts file:

describe('createD3TimeFormatter(config)', () => {
  // Existing test cases...

  // Add new test cases for the new date format
  describe('when using new date format', () => {
    const newDateFormat = '%d.%m.%Y'; // Example new date format
    it('formats date with new format', () => {
      const formatter = createD3TimeFormatter({
        formatString: newDateFormat,
      });
      expect(formatter.format(new Date(2023, 9, 5))).toEqual('05.10.2023');
    });
  });
});

This will help ensure that the new date formats are correctly implemented and tested ^[4].

To continue talking to Dosu, mention @dosu.

[maximStafeevAtAllscripts]

@dosu but I'm using docker image. I do not build superset from sources. I cannot make changes in UI that way.

[sfirke]

@mujohiddin @maximStafeevAtAllscripts am I understanding this correctly that the core problem here is that we can't type or copy-paste into that field? We should be able to put any valid D3 string in that field but despite being formatted like a field that accepts text, it functions as a dropdown.

I'm experiencing this in 4.0.2. If you agree we are talking about the same issue, I will re-write this one to clarify it.

[goldjee]

we can't type or copy-paste into that field?

I have a similar case, and for me, this is the issue. Also, I've found out that we can define default D3 formatting for numbers in superset_config.py, like so:

D3_FORMAT = {
    "decimal": ".",
    "thousands": " ",
    "grouping": [3],
}

Is there a similar option to define default formatting for dates?

[maximStafeevAtAllscripts]

@sfirke yes, accepting any valid D3 string in that field in the UI would be a solution. Also why not allow extending that predefined array in superset_config.py?

[President]

This limitation is quite frustrating. It’s unclear why users are restricted to selecting from a set of predefined formats, especially when these formats don’t align with the date conventions of many countries. It would be much more flexible to allow custom date formats, enabling users to specify the exact format they need.

In countries with non-standard formats, such as DD.MM.YYYY or similar, this restriction prevents compliance with local conventions and adds unnecessary friction. Could we explore adding a free-form date format option here?

[sfirke]

Yes I think everyone agrees it would be nice to have open text allowed here like we do for number formatting. I don't think it's a design decision, more of an oversight.

It's just a matter of someone doing it. If anyone feels able to take this on, please go for it and open a PR.

[rusackas]

This PR seems to have solved this one!