0

I'm trying to count the days since a post was made. SQL saves the date and time in the table pins in the column time. The format is 2013-11-20 10:36:01.

I have read up on how to do this on http://www.w3schools.com/sql/func_date_format.asp

and have come up with the following code:

<?php 
$dayssince = mysql_query("SELECT DATEDIFF(day,'CURDATE()','$pinDetails->time') AS DiffDate     FROM pins WHERE id ='$pinDetails->id'");
?>
<?php echo $dayssince; ?>

Where $pinDetails->time is the datestamp in the database.

However, nothing shows, not even an error number.

EDIT:

I got it to working using this code:

$result = mysql_query("SELECT DATEDIFF(CURDATE(), '$pinDetails->time')");
$dayssince = mysql_result($result, 0);
Dalían
  • 222
  • 3
  • 4
  • 14
  • 4
    1. [Stop using w3schools.](http://www.w3fools.com) 2. [Search on StackOverflow for your problem.](http://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql) – Kermit Nov 20 '13 at 17:59
  • I searched on Stack first. I even read the question edited into my own "This question may already have an answer here", but they are different since I'm using variables and not numbers, and CURDATE() wouldn't work. – Dalían Nov 20 '13 at 18:04
  • Server version: 5.0.96-log – Dalían Nov 20 '13 at 18:09
  • 2
    Your code points to SQL Server (the driver) yet you've noted what appears to be a MySQL version number. – swasheck Nov 20 '13 at 18:10

1 Answers1

8

You're using SQL Server function syntax in MySQL. The correct syntax for MySQL's DATEDIFF is:

DATEDIFF(expr1,expr2)

For example, 

SELECT DATEDIFF(CURDATE(), '2013-11-10 23:59:59')

Returns

> 10

You can replace the static date in the example with the name of the column from your table.

See a demo

Use the DBMS documentation

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • All I'm getting is "Resource id #58", I've tried changing all aspects of the code. I think when I'm calling the query I'm experiencing the problems. – Dalían Nov 20 '13 at 18:25
  • I don't know what your complete code looks like. Use pastebin or similar and provide a link for us. – Kermit Nov 20 '13 at 18:28
  • I got it working using this code: $result = mysql_query("SELECT DATEDIFF(CURDATE(), '$pinDetails->time')"); $dayssince = mysql_result($result, 0); – Dalían Nov 20 '13 at 20:08
  • You could have just done this on the application side. – Kermit Nov 20 '13 at 20:16