CODEWITHSUNDEEP
phpmysqlsql
letsCode
letsCode

Reputation: 3046

Select * from database table for the last 30 days doesnt return all elements

For Android, the date is saved like this Fri Apr 12 10:16:01 EDT 2019 and on iOs, its saved like this 2019-10-16 00:27:12 0000.

The problem is, when I query the DB

SELECT *
FROM appsearch
WHERE username = '$userID' and
       date >= CURRENT_DATE - INTERVAL 200 DAY
ORDER BY date DESC

It wont pick up the items from the Android search, only IOS. How can I pull both? I know fixing the API would be best, but im past that.

Upvotes: 0

Views: 69

Answers (1)

Nick
Nick

Reputation: 147146

Since your Android dates are not in a form which can be compared to a MySQL DATE, you must convert them first. You can use STR_TO_DATE for this, using LEFT and RIGHT to cut out the day, month and year parts of the string:

SELECT *
FROM appsearch
WHERE username = 1 AND
      (DATE(date) >= CURRENT_DATE - INTERVAL 200 DAY OR
       STR_TO_DATE(CONCAT(LEFT(date, 10), RIGHT(date, 4)), '%a %b %e %Y') >= CURRENT_DATE - INTERVAL 200 DAY
       )
ORDER BY COALESCE(DATE(date), STR_TO_DATE(CONCAT(LEFT(date, 10), RIGHT(date, 4)), '%a %b %e %Y')) DESC

Demo on dbfiddle

Note

  1. We cut out the middle part of the time string since I presume the timezone is not constant, and STR_TO_DATE requires that literal characters in the format string match exactly, so you would otherwise require multiple different format strings (e.g. for EDT, PST, etc.) to convert all of them.
  2. You need to change the ORDER BY clause to use the date value that was being used in the WHERE clause.

Upvotes: 1

Related Questions