Date subtraction today - start date in PHP and bring result ex: 1 day 5hs and 25min

Question

I have 2 variables in PHP. The first is the order date $datarequested and the second is the date now $now. I need to list how long the order has been open.

$datapedido is consulted in MYSQL.

Both are in the same format: ('Y-m-d H:i').

I wanted, in php, to make an account of $now - $datapedido and have a result, for example, of 1 day, 5hs and 25min, if possible, already do this in the query of mysql so I can sort by what is the longest open.

Answer 1

Table used

Consultation held day 31/08 at 21:29hs from the server

SELECT
CONCAT(
TIMESTAMPDIFF(day,NOME_COLUNA,now()) , ' dias ',
MOD( TIMESTAMPDIFF(hour,NOME_COLUNA,now()), 24), ' hs ',
MOD( TIMESTAMPDIFF(minute,NOME_COLUNA,now()), 60), ' min '
) from NOME_TABELA

Upshot

• CONCAT function to join query values
• TIMESTAMPDIFF - function to calculate the difference between dates

Order of consultation

For alphanumeric fields 10 comes before a 2 because the evaluation is done from left to right type by digit.

Table used:

Upshot:

With numeric values the default order is from the lowest to the highest, unless specified DESC in order by

To order the result, since it is always started by a number, you can convert the result of the query into a number that the sql will capture the part before the letters and convert to a number, then sort the result by this value.

Consultation:

SELECT
CONCAT(
TIMESTAMPDIFF(day,NOME_COLUNA,now()) , ' dias ',
MOD( TIMESTAMPDIFF(hour,NOME_COLUNA,now()), 24), ' hs ',
MOD( TIMESTAMPDIFF(minute,NOME_COLUNA,now()), 60), ' min '
) as resultado from NOME_TABELA ORDER BY CAST(resultado AS UNSIGNED INTEGER)