MySQL date query

Question

My table looks like this:

Name     Created
ganesh   2010-06-25 10:54:54 
vasant   2010-06-25 11:54:54 
charlie  2010-06-25 12:54:54 
sam      2010-06-25 13:54:54 
vasamtj  2010-06-25 01:54:54 
jack     2010-06-25 02:54:54 
nima     2010-06-24 10:54:54 
kumar    2010-06-24 10:54:54 
jay      2010-06-24 10:54:54 
paala    2010-06-23 10:54:54 
test1    2010-06-23 10:54:54 
jhon     2010-06-22 10:54:54

I want to show only today's entries, in where condition I don't want to add time:

SELECT  response FROM ost_ticket_response WHERE staff_id = 1 AND created ='now()'

this query checking with time also.

Željko Živković · Accepted Answer · 2010-06-25 10:18:33Z

1

Your table information is not complete but this query will do what you need:

SELECT *
FROM ost_ticket_response
WHERE DATE_FORMAT(Created, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

Instead of DATE_FORMAT(NOW(), '%Y-%m-%d') you can just pass string value '2010-06-25' from your code

answered Jun 25, 2010 at 10:18

Željko Živković

4,4882 gold badges24 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

baton · Accepted Answer · 2010-06-25 10:18:52Z

1

very rude, but:

SELECT response FROM ost_ticket_response WHERE staff_id = 1 AND created BETWEEN from_days(to_days(now())) AND from_days(to_days(now())+1)

answered Jun 25, 2010 at 10:18

baton

2971 silver badge3 bronze badges

1 Comment

ceteras Over a year ago

This is the only correct answer that doesn't cause a full table scan (if correctly indexed).

Mark Baker · Accepted Answer · 2010-06-25 10:19:36Z

0

WHERE Created >= date_format(CURRENT_TIMESTAMP(),'%Y%m%d000000')

answered Jun 25, 2010 at 10:19

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Jason Plank · Accepted Answer · 2011-03-11 02:37:58Z

0

Why not:

SELECT response FROM ost_ticket_response WHERE staff_id = 1 AND DATE(created) = CURDATE()

edited Mar 11, 2011 at 2:37

Jason Plank

2,3325 gold badges32 silver badges40 bronze badges

answered Jun 25, 2010 at 11:02

zelenoff

1

Comments

Jason Plank · Accepted Answer · 2011-03-11 02:42:19Z

0

This query fixed my problem:

SELECT count( response_id ) as cnt, staff_id FROM ost_ticket_response WHERE CAST( created AS Date ) = CURRENT_DATE( ) GROUP BY staff_id

Does this query have any drawbacks?

edited Mar 11, 2011 at 2:42

Jason Plank

2,3325 gold badges32 silver badges40 bronze badges

answered Jun 25, 2010 at 10:51

Bharanikumar

25.8k51 gold badges135 silver badges203 bronze badges

Collectives™ on Stack Overflow

MySQL date query

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related