Menu
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?
846
A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type. Additionally, a TIMESTAMP WITHOUT TIME ZONE value can be compared with a TIMESTAMP WITH TIME ZONE value.
MySQL has the ability to compare two different dates written as a string expression. When you need to compare dates between a date column and an arbitrary date, you can use the DATE() function to extract the date part from your column and compare it with a string that represents your desired date.
Learn MySQL from scratch for Data Science and Analytics Range − Datetime data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. But timestamp data type supports a date along with time in the range between '1970-01-01 00:00:01' to '2038-01-19 08:44:07'.
MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
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.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
