Comparing two ISO datetime strings in MySQL

user_6870913 0
Viewed 915

I have a table where there are only DateTime stored as varchar's in ISO Format. I wanted to know what would be the MySQL query to compare given any random DateTime in iso format with the DateTime(in varchar) stored in the table.

time date mysql sql string
2 Answers
SELECT
  STR_TO_DATE('2020-01-03 17:35:48', GET_FORMAT(DATETIME,'ISO')) <
  STR_TO_DATE('2020-02-01 00:00:00', GET_FORMAT(DATETIME,'ISO'));

GET_FORMAT(DATETIME,'ISO') being similar to: %Y-%m-%d %H:%i:%s, you can also do:

SELECT
  STR_TO_DATE('2020-01-03 17:35:48', '%Y-%m-%d %H:%i:%s') <
  STR_TO_DATE('2020-02-01 00:00:00', '%Y-%m-%d %H:%i:%s');

Once you have a DateTime, you can compare them using all operators: =, <, <=, >, >=, !=, BETWEEN,...

Note: Avoid to store date/time in a VARCHAR column, because:

  • You won't be able to take advantage of indexes, STR_TO_DATE(mydate) will have to be performed on every rows of your table and will cause a full table scan. The index will contain mydate, but not the result of STR_TO_DATE(mydate), which means it will not be used.
  • This will:
    • increase the complexity of all of your queries.
    • increase the amount of space used by your tables.
    • hurt the general performance of your database.

Another alternative is to avoid the transformation into date and benefit from the fact that ISO format can be sorted using ASCII comparison. In your specific case, you could do:

SELECT *
FROM TABLE
WHERE traceTime < '2020-02-21T08:40:07.749060'

But still, would be better if traceTime was of type DATETIME, the query would just be the same.

user_2014530
user_35223120
Related