Increment mysql database table column by +1 continuously in every 24 hours

[David_133]

I have db table called subscription and a column called duration. I want the column duration to be incremented by +1 in every 24 hours until it gets to 30 then stop. I would love the query to start execution if the column duration is = 1.

[pty]

I think you're approaching this from the wrong direction. You should be storing the start date and calculating the increment using datediff in a computed column.

If you need more complicated logic (like zero days being displayed as one day), take care of that in the application and just store the start date in the database.

[rproffitt]

I think pty has it right. Some formula using datediff and then some if > 30 then 30.

[Reverend Jim]

I'm assuming that >30 means expired so why not just do a case in the query that returns a value for Expired (or unexpired) of True or False. That way if the subscription period changes, all that would need to be modified is a stored query. For that matter, instead of a stored query the OP could just create a view which calculates the True/False value as a field named Expired.

[overwraith]

Right, you could have your class that is pulled from the table implementing a calculated method based on that date diff (a getter). I use object relational mappers. If you need to query such values you use sql functionality to calculate that date diff.

[prof php]

you can add 2 columns subscription_start_date and subscription_end_date to your table. Then use SELECT CASE WHEN DATEDIFF(subscription_start_date, subscription_end_date) > 0 THEN 0 ELSE 1 END AS expired FROM ... If expired is 0 then you know it's still active, and inactive if expired is 1