phpmyadmin Question posted in
The official documentation can be found here.

Mysql: No results on query after STR_TO_DATE conversion – Phpmyadmin

I have a table, with string on one of the columns, the format of the string is ISO 8601 (with TZ).

When I’m trying to run the following query:

select * from `orders` where STR_TO_DATE('last_ship_date', '%Y-%m-%dT%TZ') >= STR_TO_DATE('2020-06-27 21:13:14', '%Y-%m-%d %h:%i:%s')

I have no results, even though I have in: "last_ship_date" column the dates with the same format and on range (after June 27), for example: 2020-08-09T06:59:59Z

What am I missing here? Why I can not select the rows after STR_TO_DATE conversion?

Thank you.

Tags:

3

Answers


  1. 0

    There are 2 problems with your code:

    (1) the column last_ship_date is enclosed in single quotes so it is considered as a string literal and not a column’s name

    (2) in the format for the 2nd date you must use %H instead of %h because the hour in your column is in 24H format.

    So change to this:

    select * from `orders` 
where STR_TO_DATE(last_ship_date, '%Y-%m-%dT%TZ') >= 
      STR_TO_DATE('2020-06-27 21:13:14', '%Y-%m-%d %H:%i:%s')

    but you could also simplify to:

    select * from `orders` 
where STR_TO_DATE(last_ship_date, '%Y-%m-%dT%TZ') >= '2020-06-27 21:13:14'
    Login or Signup to reply.
  2. 0

    Your first effort should go into fixing your data model, and store dates as dates rather than strings. Using the proper datatype, you would not even face this issue.

    That said: it will always be faster not to apply a function on the column you filter on. Your string dates have a format that can be used to compare and sort, so you would rather convert the parameter to the target format:

    select * 
from `orders`
where last_ship_date = DATE_FORMAT('2020-06-27 21:13:14', '%Y-%m-%dT%H:%i:%sZ')

    This predicate would take advantage of an index on last_ship_date.

    Login or Signup to reply.
  3. 0

    One of the nice things about ISO 8601 date/time formatting is that they can be compared lexicographically. So there’s no need to call STR_TO_DATE() in the first place. Just add the T delimiter and Z zone to the string you want to compare with.

    select * from `orders` 
where last_ship_date' >= '2020-06-27T21:13:14Z'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search