Amount of time that passed

LordRogaine

on my site members log in, and when they log in their login time is recorded. This value is "last_login" How can I take this value from when they logged in and echo the amount of time that passed from their login to present.

this is what I get now.

last logged in 2002-12-28 01:03:30

How can I make it

last logged in 2days 10hours blah blah blah...ago

this is how the value is setup in my database
last_login datetime NOT NULL default '0000-00-00 00:00:00',

jc94062

One would be to grab that value and then modify it, either easily with date type functions or even by substr fuctions.

Thats not to be recommended (nor is rewritting a date function of your own!)

However, there IS an easier way... (well, in my opinion its easier!)

www.mysql.com -> search -> DATE takes you to:
http://www.mysql.com/doc/en/Date_and_time_functions.html

Its pretty easy to understand there but then I've been using that for years so if it doesn't make sense drop me a reply and I'll give you some help on the exact way to get that done 🙂

LordRogaine

Ok I give. I really wanted to try and figure this out since you said it was easy---but I can't do it.
I tried subtract functions, SELECT PERIOD_DIFF(); still can't seem to get it. What is the proper format for this? Thanks for the link it was very helpful to me in other ways.

jc94062

<?
$query = mysql_query("select YEAR(date) AS tempYear from table");
while ($rows=mysql_fetch_array($query))
{
    echo $rows['tempYear'];
}
?>

and so forth, YEAR, MONTH, etc...

They're all on that page 🙂

LordRogaine

thanks!! I'll give it a try

suntra

A little tip : when you store time in database, use time() which will return something like 1041111753. After, you retrieve that value (suppose in $time) and make a substraction : time() - $time to have the time passed since $time. Use date functions such as date() to print it like 2002-12-28 01:03:30 . It's very easier this way !

jc94062

You can switch between unix and "date stamps" via mktime and date easily anyways, so it doesn't really make too much of a difference.

It depends whether you want to store the actual date of something, or want to use a field to work out when, for example, someone last logged in\last visited a board etc... 🙂

If I'm storing the date of a mb post, I'll use datetime, no point messing around reformatting a unix stamp, but when I'm logging activity dates, I'll use unix stamps as thats the easiest way for that sitation, now()-$latime or whatever 🙂

LordRogaine

I'm sorry but I don't understand the example you gave me.

$query = mysql_query("select YEAR(date) AS tempYear from table");
while ($rows=mysql_fetch_array($query))
{
echo $rows['tempYear'];
}

I want to get the hour difference so I'm changing it to this

$query = mysql_query("select hour(time) AS temphour from table");
while ($rows=mysql_fetch_array($query))
{
echo $rows['temphour'];
}

is that correct? What about the field where I am getting my info from "last_login"? I don't understand how this gives me the difference in time value from the login time to present time now()

Another question if I'm not pushing it.

I'm trying to get a print out of the date and time in a certain format. I'm using this

select date_format('last_login', '%W %M %d, %Y %I:%i:S %p') from table where username= '$username';

but I keep getting blan results. How do I get the script to accept the the time value from the field "last_login"?