Filter sql results based on Timestamp

Question

I have the following CODE

$thedate = "2018-06-04"; // YYYY-MM-DD

SELECT * 
FROM (`leadactivity`) 
WHERE statusdate = $thedate 
ORDER BY id DESC 
LIMIT 25

I am trying to only show the results where statusdate is = to the date provided. However because in sql the column autoupdates with Timestamp, it's including the time, and for some reason is just not giving any results.

Any ideas on what I am doing wrong?

Which database? You need to convert your datatime to a date or match using wildcards (which may be a lot slower on larger databases). Datetime functions are slightly different for each engine - if this SQL Server it's a duplicate of [this question](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype?rq=1) — Alan, Jun 16 '18 at 23:57

score 5 · Accepted Answer · answered Jun 17 '18 at 04:08

5

Your code looks like MySQL. One simple way is:

SELECT la.*
FROM leadactivity la
WHERE DATE(la.statusdate) = $thedate 
ORDER BY id DESC
LIMIT 25;

However, the use of the DATE() function prevents the use of an index. So, a better approach is:

SELECT la.*
FROM leadactivity la
WHERE la.statusdate >= $thedate AND la.statusdate < $thedate + interval 1 day
ORDER BY la.id DESC
LIMIT 25;

answered Jun 17 '18 at 04:08

Gordon Linoff

1,242,037
58
646
786

Best solution for mysql query crack through php – Abijith Ajayan May 31 '22 at 12:44

score 1 · Answer 2 · answered Jun 16 '18 at 23:54

1

try this:

          SELECT * FROM (`leadactivity`) 
          WHERE  statusdate like  '%"$thedate"%' 
          ORDER BY id DESC LIMIT 25

answered Jun 16 '18 at 23:54

Cheikh HAIBALA

125
1
11

score 1 · Answer 3 · answered Jun 17 '18 at 00:02

When comparing timestamps with date values, a time-of-day of midnight (ie 00:00:00) is assumed. Specify a range of timestamps based on the date from 00:00:00 to 23:59:59, like this:

SELECT * FROM (`leadactivity`)
WHERE statusdate between '$thedate 00:00:00' and '$thedate 23:59:59'
ORDER BY id DESC LIMIT 25

Depending on your language and database, the syntax to achieve this will vary.

Although converting the timestamp to a date before comparing is simpler, your database won’t use the index on the timestamp column (if one exists), resulting in the worst performance.

Filter sql results based on Timestamp

3 Answers3