CODEWITHSUNDEEP
mysqldate
Noweem
Noweem

Reputation: 123

calcutate membership duration, until now or until end date

I have a MySQL table with (among others) the following columns:

[name] [member_since_date] [member_until_date]

When somebody's membership ends, the [member_until_date] field is populated, otherwise it contains NULL.

I need a purely SQL based solution for the following:

I want to calculate how long someone is a member: when [member_until_date] is filled, I need it to calculate [member_until_date] - [member_since_date].

When somebody is still a member, the field [member_until_date] is NULL, so then I need it to calculate [NOW] - [member_since_date].

I hope I'm clear enough on this, and I hope somebody has an answer for me.

Upvotes: 1

Views: 336

Answers (2)

Ami
Ami

Reputation: 1254

To get the difference between dates, in days, use DATEDIFF(). To take one value based on a condition, or another, use IF, though in this case I am using the similar IFNULL().

SELECT DATEDIFF(IFNULL(member_until_date, NOW()), member_since_date) AS days_member
FROM ...

IFNULL() says use the first argument, unless it's null, then use the second argument.

DATEDIFF() expects the larger date first in order to get a positive result.

COALESCE() provides similar functionality to IFNULL() and would be the ANSI SQL way of doing this.

Upvotes: 2

kba
kba

Reputation: 19466

Here's a solution using DATEDIFF().

SELECT name, DATEDIFF(IF(member_until_date,member_until_date,NOW()),
   member_since_date) AS membership_duration
FROM members;

First, we check if member_until_date is null. If it is, we use NOW(), otherwise we use member_until_date.

IF(member_until_date,member_until_date,NOW())

Now we calculate the date difference between the above and the beginning of the membership, member_since_date, and return is as membership_duration.

DATEDIFF(IF(member_until_date,member_until_date,NOW()),
   member_since_date) AS membership_duration

Upvotes: 0

Related Questions