CODEWITHSUNDEEP
phpmysqlsql
Finglish
Finglish

Reputation: 9956

How to update mysql row where timestamp field is today?

I want to update a row in a MySql database where a timestamp field value is within the current day.

e.g. something like

UPDATE records 
   SET firstname="bob" 
 WHERE lastname="jones" 
   AND timer=[today]

Upvotes: 2

Views: 305

Answers (5)

flaschenpost
flaschenpost

Reputation: 2235

All solutions that cast or modify the field timer will never let an index work, that means those queries will be slow. Alike @Stevens answer I would write:

UPDATE records 
   SET firstname="bob" 
 WHERE lastname="jones" 
   AND timer >= cast(curdate() as datetime) 
   AND timer < cast(curdate() + interval 1 day as datetime);

Then you have the right day at every time.

EDIT: Wrong was

   AND timer => cast(curdate() as datetime)

corrected was

   AND timer >= cast(curdate() as datetime)

Upvotes: 2

amacks
amacks

Reputation: 80

I would do it with

where date(timer) = date(now())

note that will possibly do odd things if you have timezone changes, in which case I'd change everything into epoch, i.e.

where unix_timestamp(...pseudo code to get the proper midnight) <= unix_timestamp(timer)

Upvotes: 0

Pirion
Pirion

Reputation: 519

You can cast to a DATE to drop off the time portion and then compare just this.

UPDATE records SET firstname="bob" WHERE lastname="jones" AND CAST(timer AS DATE) = CAST(NOW() AS DATE)

Upvotes: 0

Scott C Wilson
Scott C Wilson

Reputation: 20016

You should be able to use DATE_SUB(NOW()) in your update.

Here's the help: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

Upvotes: 0

Steven Moseley
Steven Moseley

Reputation: 16325

This will be your best-performing option (calculating the date range externally and using a BETWEEN). Casting every row of your table will be significantly slower.

UPDATE records SET firstname="bob" 
WHERE lastname="jones" 
    AND timer BETWEEN '2013-05-26' AND '2013-05-27'

Upvotes: 1

Related Questions