Repository: tobymao/sqlglot

Issue:
Mysql dialect's TIME_MAPPING incorrectly maps %W
**Before you file an issue**


%a in the query converted to %W.
E.g. `select date_format(timestamp('2024-08-22 14:53:12'), '%a')`
output is converted to:
`select date_format(timestamp('2024-08-22 14:53:12'), '%W')`

mysql> select date_format(timestamp('2024-08-22 14:53:12'), '%W');
+-----------------------------------------------------+
| date_format(timestamp('2024-08-22 14:53:12'), '%W') |
+-----------------------------------------------------+
| Thursday                                            |
+-----------------------------------------------------+
1 row in set (0.02 sec)

mysql> select date_format(timestamp('2024-08-22 14:53:12'), '%a');
+-----------------------------------------------------+
| date_format(timestamp('2024-08-22 14:53:12'), '%a') |
+-----------------------------------------------------+
| Thu                                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)



**Official Documentation**
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior

According to the python `strftime` formats documentation:
%A is Weekday as locale’s full name.(Sunday, Monday...)
%a is `Weekday as locale’s abbreviated name` (Sun .. Sat)


Let’s look at mysql:
%W Weekday name (Sunday..Saturday)
%a:Abbreviated weekday name (Sun .. Sat)

So, it is expected that the date_format output in presto or mysql should map %W to %A, as follows:
`mysql.py`
```
time_mapping = {
"%M": "%B",
"%c": "%-m",
"%e": "%-d",
"%h": "%I",
"%i": "%M",
"%s": "%S",
"%S": "%S",
"%u": "%W",
"%k": "%-H",
"%l": "%-I",
"%T": "%H:%M:%S",
- "%W": "%a",
+ "%W": "%A",
}
```
I observed that several other mappings worked well. If applied, I noticed that mysql and snowflack were affected in the single test. Then this may affect more, such as drill, hive, oracle, teradata, which also use %a to represent the pattern of Weekday as locale’s full name.

Why is this the case?

In addition, there is a PR #3855 in presto that does the conversion. Is this a workaround?
@VaggelisD PTAL


Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index d7a2d926..5dde49ae 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -173,7 +173,7 @@ class MySQL(Dialect):
         "%k": "%-H",
         "%l": "%-I",
         "%T": "%H:%M:%S",
-        "%W": "%a",
+        "%W": "%A",
     }
 
     class Tokenizer(tokens.Tokenizer):
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index ed565503..9d4e8962 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -240,10 +240,7 @@ class Presto(Dialect):
     TABLESAMPLE_SIZE_IS_PERCENT = True
     LOG_BASE_FIRST: t.Optional[bool] = None
 
-    TIME_MAPPING = {
-        **MySQL.TIME_MAPPING,
-        "%W": "%A",
-    }
+    TIME_MAPPING = MySQL.TIME_MAPPING
 
     # https://github.com/trinodb/trino/issues/17
     # https://github.com/trinodb/trino/issues/12289


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 45b79bf6..3ef0b0e2 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -539,9 +539,16 @@ class TestMySQL(Validator):
             },
         )
         self.validate_all(
-            "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
+            "SELECT DATE_FORMAT('2024-08-22 14:53:12', '%a')",
             write={
-                "mysql": "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
+                "mysql": "SELECT DATE_FORMAT('2024-08-22 14:53:12', '%a')",
+                "snowflake": "SELECT TO_CHAR(CAST('2024-08-22 14:53:12' AS TIMESTAMP), 'DY')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%a %M %Y')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%a %M %Y')",
                 "snowflake": "SELECT TO_CHAR(CAST('2009-10-04 22:23:00' AS TIMESTAMP), 'DY mmmm yyyy')",
             },
         )
@@ -555,7 +562,7 @@ class TestMySQL(Validator):
         self.validate_all(
             "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %a %d %m %b')",
             write={
-                "mysql": "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %W %d %m %b')",
+                "mysql": "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %a %d %m %b')",
                 "snowflake": "SELECT TO_CHAR(CAST('1900-10-04 22:23:00' AS TIMESTAMP), 'DD yy DY DD mm mon')",
             },
         )