billyhafiz billyhafiz - 2 months ago 9
SQL Question

How to get the time elapsed in seconds between two timestamps in dql?

I'm using Symfony and Doctrine. I'd like to get the time elapsed between two timestamps. Here is a portion of my query (both a.date and q.date are type: timestamp):

$qb->select('a.date - q.date AS elapsed_time');


This gives a numerical result, but I can't tell what the units are. 9 seconds gave me 49, and 60 seconds gave me 99; I can't make sense of that.

I tried this too:

$qb->select('DATE_DIFF(a.date, q.date) AS elapsed_time');


This works, but gives the result in days. I really need minutes or seconds.

Answer

Use UNIX_TIMESTAMP instead. try this:

$qb->select('(UNIX_TIMESTAMP(a.date) - UNIX_TIMESTAMP(q.date)) AS elapsed_time');
Comments