How to perform a query that contains DATE_FORMAT syntax with PHP PicoDb query builder?

Question

I am coding in a system (Kanboard) that has as background the PicoDb database query builder. So, I need to perform a specific MySQL query.

My table is something like so:

id, title, date_moved
int, varchar, UNIX_TIMESTAMP

So, I want to subtract the current timestamp time from the 'date_moved' column. After that, I want to transform the result in days.

Everything goes fine, but outside of the system when I perform the query in a MySQL client.

SELECT title, DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()- date_moved), ‘%d’) AS ‘Age’ 
FROM 
    tasks 
WHERE 
    project_id = HERE_COMES_THE_PROJECT_ID 
ORDER BY 
   Age

But, has anybody an idea about how to perform it via PicoDB?

Information regarding PicoDB

https://github.com/elvanto/picodb

Answer 1

Try to build query this ways if its giving you exact what you want.

$db->table('tasks')->subquery('DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()- date_moved), ‘%d’)', 'Age')->buildSelectQuery();

Then you should try with executing query like this.

$db->table('tasks')->subquery('DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()- date_moved), ‘%d’)', 'Age')->eq('project_id', 42)->asc('Age')->findAll()