Calculating member since time in ExpressionEngine

Posted on April 5, 2013

Sometime it can be required to calculate the time since an user has joined the website or registered. ExpressionEngine stores member’s registration date/time with member data so we can calculate the time (in years, months and days) since member had registered.

It will be better to use Query Module to perform the SQL query into template.


{exp:query sql="SELECT
FLOOR(DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365) AS since_years,
FLOOR((DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365 - FLOOR(DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365))* 12) AS since_months,
CEILING((((DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365 - FLOOR(DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365))* 12) - FLOOR((DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365 - FLOOR(DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date))/365))* 12))* 30) AS since_days
FROM exp_members
WHERE member_id = {member_id}"}

[Member Name] is member since {since_years} years, {since_months} months and {since_days} days.

{/exp:query}