View Full Version : time in database to return certian value

02-19-2012, 06:21 PM
sorry if the title isnt very clear, i just didnt know how to word it, anyways, the point of this topics is, that im wondering how to pull a quesry from the database and have it display either "Today", "Yesterday", "1 Day Ago", "4 days ago" ect ect, 4 in that last one being X ammount of days, i am trying to get it from the value of this feild in the database:


which produces a value in the format of date and time like so:

2012-02-19 05:14:31

so my question is, how exactly would i do this, as im not very good with the time functions and quesrys in php.

Cheers if you can help, also if you need more explanations please just ask :)


02-20-2012, 11:44 AM
there is certainly a function in SQL that lets you get the difference in days. then you can either use a SQL procedure to convert 0 into today and 1 into yesterday or you do that part in PHP.

PS. I deem "yesterday" and "1 day ago" synonymous

02-20-2012, 07:00 PM
PS. I deem "yesterday" and "1 day ago" synonymous

that is true, i was using it as an example :) but you are completely correct.


02-22-2012, 12:27 PM
can anyone help me with this please?

02-22-2012, 01:53 PM
what do you need help with? the MySQL date/time functions are here (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html).

02-22-2012, 05:37 PM
well what i need help on is whats in my OP, basically converting the time stamp, from what it is, to something like "Yesterday", "2 Days Ago", "3 Days Ago" so on, so forth.

02-22-2012, 06:50 PM
the first step would be calculating the date difference. for that I have given you a link. once you have that it’s just a matter of if()s to make that into a human understandable string.

02-22-2012, 07:04 PM
on that note, is this a reasonable approach?

$eachday = 86400;

$days= $utime-86400;

02-22-2012, 07:05 PM
I would do the date difference calculation in SQL. it has that many date functions for a reason.

02-22-2012, 07:08 PM
im sorry, i dont really understand, ive only recently started learning different methods of using sql, such as table joins, so could you possibly, if you dont mind, show me some sort of example?

02-22-2012, 07:19 PM
let’s use a slightly off-topic date function to demonstrate

-- show all dates in european format that are not older than one week

PS. there is a function that does exactly what you want.

02-22-2012, 07:23 PM
is this right?

SELECT TO_DAYS(lastseen) FROM users WHERE username='$viewuser'

02-22-2012, 07:25 PM
does the result meet your expectation?

02-22-2012, 07:46 PM
well ive tried this:

$querytestnew = mysql_query("SELECT TO_DAYS(lastseen) FROM users WHERE username='DOS1392'");
$fetchnewnew = mysql_fetch_object($querytestnew);
echo "$fetchnewnew->lastseen";

however its not displaying anything at all, have i gone wrong somewhere?

02-22-2012, 07:48 PM
yes, twice.
- there is no lastseen property in your fetched object
- property calls do not work inside strings

hint: look 3 functions up

02-22-2012, 07:49 PM
are you refering to the "TIMESTAMP()" one?

02-22-2012, 07:51 PM
nope, but almost.

02-22-2012, 07:53 PM

so if i was to write it like this:

$querytestnew = mysql_query("SELECT TIMEDIFF(lastseen) FROM users WHERE username='DOS1392'");
$fetchnewnew = mysql_fetch_object($querytestnew);
echo "$fetchnewnew";

i still get the following error:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/crimewav/domains/crime-wave.co.uk/public_html/profile.php on line 44

02-22-2012, 07:57 PM
obviously, look at what the function call is supposed to be.

TIMEDIFF() is not bad, but there is a better function

02-22-2012, 08:03 PM
is it something to do with putting the NOW(); in the query?

02-22-2012, 08:06 PM
that’s quite a good idea.

02-22-2012, 08:09 PM
going by what your saying, i tried this:

$querytestnew = mysql_query("SELECT TIMESTAMPDIFF('lastseen') FROM users WHERE username='DOS1392' AND DATE_ADD(lastseen < NOW();");

is this how i would write it?

02-22-2012, 08:10 PM
what does the manual say how to write it?

02-22-2012, 08:14 PM
ok, sorry about the confusion on the last one, i think ive got it now, after reading how to write it, ive got this:

$date = gmdate('Y-m-d h:i:s');
$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'lastseen','$date') FROM users WHERE username='DOS1392'");

02-22-2012, 08:16 PM
now the only problem is that 'lastseen' is not a date(string) but an ordinary string. besides that you can replace '$date' by NOW().

02-22-2012, 08:19 PM
is it possible to write a function, like the TIMESTAMPDIFF() inside a previous one?

02-22-2012, 08:44 PM
as long as the return value matches the expected input—yes.

02-22-2012, 08:45 PM
so would this be right:

$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'DATE('lastseen')','$date') FROM users WHERE username='DOS1392'");

02-22-2012, 08:47 PM
you’re turning in circles. TIMESTAMPDIFF() expects a date, not a string.

02-22-2012, 08:49 PM
ok, i think ive got it now, as i said, im still quite new to working with just sql, but here goes, $fetch2->lastseen is from a previous call, so i replaced lastseen with that:

$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'$fetch2->lastseen','$date') FROM users WHERE username='DOS1392'");

02-22-2012, 08:49 PM
I said DATE, not string.

02-22-2012, 08:53 PM
so i would change lastseen from something like this:

2012-02-22 07:49:23

to this:


is that what you mean?

02-22-2012, 08:54 PM
nope, you can just pass in the lastseen column in.

02-22-2012, 08:55 PM
im sorry, i dont follow what you mean

02-22-2012, 08:56 PM
what about TIMESTAMPDIFF(DAY, lastseen, NOW()) ?

02-22-2012, 09:03 PM
ok so ive got that, am i still right in calling it using a fetch? if so, how would i use the operator -> eg whats after the ->

02-22-2012, 09:40 PM
originally the "column", resp. the expression, you place after the select. but fear not my friend, you can use an Alias in SQL.

SELECT something AS something_else FROM ...

PS. or you use numeral fetching

02-22-2012, 09:43 PM
ok, so i tried this:

$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY, lastseen, NOW()) AS date FROM users WHERE username='DOS1392'");
$fetchnewnew = mysql_fetch_object($querytestnew);
echo "$date";

and the result i got was:

2012-02-22 08:53:19

02-22-2012, 09:48 PM
what is the relation between $fetchnewnew = mysql_fetch_object($querytestnew); and echo "$date";? is there a relation at all?

02-22-2012, 09:50 PM
i believed it was this part:

TIMESTAMPDIFF(DAY, lastseen, NOW()) AS date

was i wrong?

sorry i realised it should have been:


so with this should i use if statements, eg:

if ($fetchnewnew->date == "0") {
$days = "Today";
}elseif ($fetchnewnew->date == "1") {
$days = "Yesterday";
$days = "$fetchnewnew->date Days Ago";

02-22-2012, 09:54 PM
and if we could get around to dropping the outdated mysql extension for the more modern MySQLi (http://php.net/mysqli) or PDO (http://php.net/pdo) extensions, that would be bliss (and even safe against SQL Injections).

02-22-2012, 09:56 PM
i edited my last post, just a quick question, what does you mean by this:

and if we could get around of dropping the outdated mysql extension for the more modern MySQLi or PDO extensions, that would be bliss (and even safe against SQL Injections).

as this is just to display some results, it wont be used for anything else, and i dont understand the PDO extensions as i dont know what they are.

02-22-2012, 09:59 PM
directly quoted from the Manual:


If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

(note: current version of MySQL is something around 5.5)

that means, only MySQLi and PDO (and not mysql) are able to make Prepared Satements, which protect against SQL Injections.

mysqli and PDO are database extensions just like mysql—only better

02-22-2012, 10:01 PM
o right, this is going to sound really stupid if im wrong, but i was always led to believe that, mysql could not be used alongside mysqli and what does PDO stand for

02-22-2012, 10:04 PM
mysql is outdated, so there is no need to use it alongside mysqli. PDO stands for PHP Data Objects and is a data-access abstraction layer (i.e. it is able to use other databases (like PostgreSQL or SQLite) as well). you’ll find more in the above link.

02-22-2012, 10:06 PM
so in reality, i should stop using mysql, and move onto learning PDO and mysqli?

02-22-2012, 10:13 PM
not necessarily both, but one. both have their pros and cons so you would need to weight your decision.

+ can get the most out of MySQL
– no Iterators, no Exceptions
– error handling must be done manually
– handling of Prepared Statements is somewhat clunky (IMO)

+ uses Iterators and Exceptions
+ errors can be handled by PDO
– used as a "common denominator" for many databases
– does not support the full MySQL functionality