CODEWITHSUNDEEP
mysqltimestamp
Swen Mulderij
Swen Mulderij

Reputation: 916

MYSQL TIMESTAMP comparison

I have a table with a column Time that stores a timestamp value, a column that stores a Name and a column that stores a Status.

I'm trying to find a query to update all entries before a given timestamp like this:

UPDATE `Table` 
SET Status=1
WHERE Name='personname' AND 'Time'<'2012-12-23 18:00:00'

The query is valid but nothing changes.

When trying to show the results of the WHERE part there are no results.

What am I doing wrong?

Upvotes: 23

Views: 81586

Answers (3)

rahul
rahul

Reputation: 1

Try This:

UPDATE `Table`SET Status=1
WHERE Name='personname' AND 
Time < UNIX_TIMESTAMP(STR_TO_DATE('23-12-2012 18:00:00',' "%d-%m-%Y %h:%i:%s'));

Upvotes: 0

Andomar
Andomar

Reputation: 238068

You're comparing the string literal 'Time':

'Time'<'2012-12-23 18:00:00'

Try comparing the time column instead:

Time < '2012-12-23 18:00:00'

Or if you have to, surround it in backticks:

`Time` < '2012-12-23 18:00:00'

Live example at SQL Fiddle.

Upvotes: 47

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

If you are sure about backticks and single quotes and still it doesnt work then try this out,

UPDATE `Table`SET Status=1
WHERE Name='personname' AND 
Time < STR_TO_DATE('2012-12-23 18:00:00','YYYY-MM-DD HH:MI:SS')

Upvotes: 3

Related Questions