0

I'm practically newbie with MySQL.
I'm trying to do a query to retrieve a preformatted date, obtained from a subtraction, using CONVERT.
This is the query:

sql = f"SELECT CONVERT(timestamp - {sqlToday}, getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

timestamp is a column of the database, and sqlToday is a date in format datetime.date(2020, 2, 13).
Unfortunately this error returns, and I don't know how to handle it:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = 'Graphic' at line 1

Thanks in advance!

  • The CONVERT syntax you have here isn't mysql. What do you want this expression to return? NOW() is the current date (and other date functions in the link). – danblack Apr 22 at 8:54
  • I have tried NOW(). It works very well, thanks @danblack! I found the convert documentation here. But at this point I believe it is totally incomplete. – Memmo Apr 22 at 9:11
  • 1
    your link is for SQL-Server. Quite different product. Some SQL is the same, some different. There's enough MySQL documentation to keep you from guessing what is/isn't correct. The good thing about MySQL/MariaDB errors is they point exactly where the problem is. – danblack Apr 22 at 9:15
  • 1
    previous links, and just noticed MariaDB in the error so linked its documentation too. Keep asking if you get stuck however. – danblack Apr 22 at 9:54
  • 1
    The error is pointing to "getdate"; look that up. When you don't find it, search for "MySQL date functions". – Rick James Apr 29 at 3:24
0

In fact, it is not easy to find the right command we need for queries in the MySQL documentation.
However, by testing, I saw that this should be fine:

sql = f"SELECT DATEDIFF(NOW(), timestamp), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

DATEDIFF(NOW(), timestamp) returns the difference between two dates in days.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.