Max Max - 2 months ago 15
MySQL Question

Datediff seconds return error

I have a variable

$date="2016-10-09 10:44:03";
and a list of messages ordered by
date_post
(
TIMESTAMP CURRENT_TIMESTAMP
). (
$date
is always lower than
date_post
for seconds)

I want to return results from

SELECT *,DATEDIFF(second, $date,date_post) AS duration
FROM messages
WHERE condition


but it returns error


supplied argument is not a valid MySQL result resource in ....


It is suppossed to return a number, but it just return error. According to the sintax,
$date
must be written first in
DATEDIFF
. I tried to use
CAST($date)
, but nothing. Do I need to modify
$date
before using it? If I work with
DATEDIFF(date_post,date_post)
returns
0
, but with
$date
returns error

Answer

If you want the difference in seconds, the easiest way is to use to_seconds():

SELECT m.*,
       ( to_seconds(date_post) - to_seconds($date) )AS duration 
FROM messages m
WHERE condition;

The function you are using is a SQL Server functions. In MySQL, datediff() returns the difference between two dates in days.