SQL中计算item_expired列与当前日期差值并存储到remaining_time列

Hey there! Let's work through your problem step by step. The main issue with your original query is how you're trying to assign the calculated value to a column—SQL uses AS to define column aliases, not the = syntax you used. Also, you mentioned wanting the result in remaining_time, but your query tried to name it item_date, which was probably a typo.

Query to Calculate and Display Remaining Time #

First, if you just want to view the remaining days alongside your other columns, here's the corrected syntax, broken down by common database systems:

For SQL Server (since you used CURRENT_TIMESTAMP) #

SELECT 
    item_description, 
    item_expired, 
    DATEDIFF(DAY, CURRENT_TIMESTAMP, item_expired) AS remaining_time
FROM Customers;

• DATEDIFF(DAY, start_date, end_date) calculates the number of days between the two dates. Here, we're subtracting the current time from item_expired to get days left until expiration.
• If item_expired is in the past, this will return a negative number (indicating how many days the item has been expired).

For MySQL #

MySQL's DATEDIFF function reverses the parameter order (it takes end_date first), so adjust the query like this:

SELECT 
    item_description, 
    item_expired, 
    DATEDIFF(item_expired, CURRENT_TIMESTAMP()) AS remaining_time
FROM Customers;

You can also use NOW() instead of CURRENT_TIMESTAMP() in MySQL—they work interchangeably here.

Storing the Result in the remaining_time Column #

If you want to persist this value in your Customers table's remaining_time column (not just display it), you'll need an UPDATE statement:

First, add the column if it doesn't exist #

If remaining_time isn't already in your table, create it first:

-- For SQL Server or MySQL
ALTER TABLE Customers ADD remaining_time INT;

Then update the values #

SQL Server

UPDATE Customers
SET remaining_time = DATEDIFF(DAY, CURRENT_TIMESTAMP, item_expired);

MySQL

UPDATE Customers
SET remaining_time = DATEDIFF(item_expired, CURRENT_TIMESTAMP());

Just a heads-up: this will overwrite any existing values in remaining_time. If you need to keep this column updated automatically, you might want to look into creating a computed column (SQL Server) or a generated column (MySQL) instead of manually updating it.

内容的提问来源于stack exchange，提问作者bbqapps