Repository: tobymao/sqlglot

Issue:
sqlglot corrupts date_format spec for MySQL
**Before you file an issue**
> - Make sure you specify the "read" dialect eg. parse_one(sql, read="spark")

Yes, `read='mysql'`

> - Check if the issue still exists on main 

Yes

**Fully reproducible code snippet**

> Please include a fully reproducible code snippet or the input sql, dialect, and expected output.

```
In [19]: import sqlglot

In [20]: sqlglot.parse_one("date_format(now(), '%Y-%m-%d %H:%i:00.0000')", read='mysql').sql(dialect='mysql')
Out[20]: "DATE_FORMAT(NOW(), '%Y-%m-%d %H:%M:00.0000')"
```

sqlglot uses `%M` specifier for minute, but in MySQL `%i` should be used.

**Official Documentation**

> Please include links to official SQL documentation related to your issue.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format


Discussion:

Patch:
diff --git a/sqlglot/time.py b/sqlglot/time.py
index c286ec1e..50ec2ec3 100644
--- a/sqlglot/time.py
+++ b/sqlglot/time.py
@@ -42,6 +42,10 @@ def format_time(
                 end -= 1
                 chars = sym
                 sym = None
+            else:
+                chars = chars[0]
+                end = start + 1
+
             start += len(chars)
             chunks.append(chars)
             current = trie


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 45bb763b..ab246a3d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -123,6 +123,7 @@ class TestMySQL(Validator):
         self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
 
     def test_identity(self):
+        self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')")
         self.validate_identity("SELECT @var1 := 1, @var2")
         self.validate_identity("UNLOCK TABLES")
         self.validate_identity("LOCK TABLES `app_fields` WRITE")