Setting subscription dates in MySQL
date, mysql, sql, timestamp
Solution
The `MIN()` and `MAX()` functions are for grouping: you want `LEAST()` and `GREATEST()` instead.
Problem
I'm dealing with a website where people can subscribe to certain things for virtual money. I need to put the dates at which subscriptions end in the database. My table has a field "expiration" for that, which is a DATE. When the user extends his subscription, I need to add 1 month to this date. However, if the subscription has already expired, I want to set "expiration" to 1 month from now, not to 1 month from when the subscription expired. I've tried: ``` UPDATE shop_user_rights SET expiration = ADDDATE(MAX(expiration, CURDATE()), INTERVAL 1 MONTH); ``` and ``` UPDATE shop_user_rights SET expiration = FROM_UNIXTIME( MIN( UNIX_TIMESTAMP(expiration), UNIX_TIMESTAMP(CURDATE()) ) ), expiration = ADDDATE(expiration, INTERVAL 1 MONTH); ``` But both give syntax errors. Is there a way to do this in 1 query, or do I have to use some SELECT queries beforehand?