In Doctrine ORM, how can I sum seconds to a timestamp field?

Question

I have a starts_at field that keeps a timestamp with the moment something starts. Also, I have another field seconds_long that represents the amount of seconds that the event lasts.

I need to select all unfinished events but I just can't find a way to sum the starts_at and seconds_long fields. I first tried to sum them, expecting Doctrine do some magic, but it didn't work:

addWhere("c.starts_at + c.seconds_long > ?", date('Y-m-d H:i:s', time()));

Unfortunately, that does not work. Then, I tried to convert the starts_at field to UNIX seconds, but I didn't find a SQL function that worked in sqlite and MySQL.

Is there a way to do this? How?

Answer 1

There's probably a simpler way to do this, but what about setting the fields you have as "starts_at" and "ends_at" - both as timestamps.

You could then get the difference with a subtraction: starts_at - ends_at > ?.

Answer 2

This should work but I didn't test it.

In your Doctrine Record class

function findFinishedOccurances()
{
    $this->getTable()-
         ->createQuery()
         ->select("c.*, DATE_ADD(c.starts_at, INTERVAL c.seconds_long SECOND) AS c.finished_at")
         ->where("c.finished_at >= ?", date('Y-m-d H:i:s', time()))
         ->execute(); 
}

Answer 3

I think it is not possible to make a query which works with both SQLite and MySQL. An ORM can make lots of stuff database independend but there's no way to translate your task to both SQLite and MySQL.

What you should do its first simplify the idea of the task. Do something in PHP to prevent you from having to build the query the way you are doing; or change the Database structure.