CODEWITHSUNDEEP
mysql
rinrin
rinrin

Reputation: 49

Automatically change a column within a specific amount of time in MySQL

I have a reset table on MySQL.

id | email | token | created_at | status

created_at is of type timestamp while status is varchar (active/inactive). By default, every insertion registers the status as active. What I want to happen is that after 10 minutes from the registered created_at, the status should automatically update to inactive.

I tried doing this

Update reset
SET `status`= 'inactive'
WHERE `created_at`> NOW() - INTERVAL 10 MINUTE;

but status still hasn't changed from active to inactive after 10 minutes.

Upvotes: 0

Views: 787

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

A better design/approach here would be to just compute the active/inactive status when you go to query:

SELECT
    created_at,
    CASE WHEN created_at >= NOW() - INTERVAL 10 MINUTE
         THEN 'active' ELSE 'inactive' END AS status
FROM reset
ORDER BY created_at;

Don't set out to continually update your data, but rather let your logic interpret the data as you need.

Edit:

If your actual requirement is to view only inactive records, then use:

SELECT *
FROM reset
WHERE created_at < NOW() - INTERVAL 10 MINUTE;

Upvotes: 1

Related Questions