Elasticsearch: SQL: Implement DATE_FORMAT function

[matriv]

Implement DATE_FORMAT according to the MySQL spec: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format by translating to the java.time patterns used in DATETIME_FORMAT.

Follows: #54832

[elasticmachine]

Pinging @elastic/es-ql (:Query Languages/SQL)

[erickmp07]

Hi. Can I start working on this?

[matriv]

@erickmp07 Yes, sure, thank you!

[MohamedBechir]

Is it possible to start working on it?

[erickmp07]

@MohamedBechir I'm working on it.
Sorry if I haven't created a PR yet, but this activity is part of my course evaluation.
My group scheduled a meeting for tomorrow qnd we're going to do this together.

[chitranshi21]

Hi Can I take this one ?

[erickmp07]

Hi, @chitranshi21 .
My group developed the solution and we're creating the tests.
We're going to create the PR soon.

[Anyrob]

Hi, @erickmp07 saw that you're assigned to this issue two months ago. Are you still working on this? If not, please let me know, I would like to take it

[Anushka9833]

Hi @erickmp07 , is this issue still available ? Can I please start working on it ?Please let me know

[erickmp07]

Hi, @Anyrob and @Anushka9833 .
You can take it.
My group are implementing a refactoring to propose.

[matriv]

@Anyrob I've assigned you.

[Anyrob]

Hey @matriv! I was making some changes to the code but then I realized something. Would you mind being more specific in the difference of the DateTimeFormat with the DateFormat, I want to be sure that I'm on the right path, I discovered that my previous analysis looked a lot like the DatePart function, so this difference doesn't seem so trivial to me now, and I would like to make it clear before continuing :)

[matriv]

DATETIME_FORMAT uses the java's DateTimeFormatter patterns, whereas DATE_FORMAT will use the patterns as specified by MySQL, e.g.: %H denotes the 24-hour format where in java is HH. So the implementation should simply accept the MySQL patterns and translate them to the corresponding java ones.

[Anyrob]

Thank you! I think I got it now! I let you know in case I have another doubt.

[matriv]

@Anyrob There is a similar PR open that addresses the MS-SQL variant of the function, so you'll only need to add the new function definition, and a new Formatter to implement the MySQL variant.

[Anyrob]

Thank you @matriv I'm still working on it, I have a few doubts about the translation of specific symbols as:

• For example, the "%U,%u,%V,%v" (MySQL) I'm replacing those as "w" (JAVA), am I right?
• Also %f (Microseconds) has no proper translation, what should I do in that case?

[matriv]

• For example, the "%U,%u,%V,%v" (MySQL) I'm replacing those as "w" (JAVA), am I right?

I'd say yes, but we need to properly document that they would all result in computing the week starting from Monday (If I remember correctly for Java's w).

• Also %f (Microseconds) has no proper translation, what should I do in that case?

I think you can simply use S and for a MySQL pattern it will go up to 6 digits so 6 Ss.

[Anyrob]

Thank you @matriv! I keep you up to date

[Anyrob]

Hey, @matriv! I just wanted to update you: I'm trying to write a test for my code based on DateTimeFormatProcessor. Today I'll make my PR, I would like that you could check it, to see If I have made the correct approach or if I need to make changes :)

[ajanthans]

Hi @matriv If this issue is still open I could take it.
Hi @Anyrob If you are continue to work on it no problem , if not let me know, I would like to pick it up.

[matriv]

@ajanthans

Hi. Thank you for your interest in contributing!
Inline with common open source practices, we would like to no longer block and attribute issues, but rather invite anyone with a concrete proposal to simply submit a PR. No one likes duplicating work, but please consider that chances of concurrent PRs are low - especially for longer standing issues - and quality will trump over submission order.