MySQL table with datetime field, how to know how many days have passed?

Question

MySQL table with datetime field, how to know how many days have passed?

Navigation

#1 by (4 votes)

1

Hello, I have a table where the following fields exist.

prod_id [int 11]
prod_nome [varchar 100]
prod_desc [textarea]
prod_valor [double 10,2]
prod_data_cadastro [datetime]

I need to create a SELECT that shows the time that passed to the current date using the prod_data_cadastro [datetime] column record.

I tried this way:

SELECT *, DATEDIFF(NOW(), 'prod_data_cadastro') as 'data_corrida' FROM 'produto' ORDER BY 'data_corrida' DESC;

But it only returns me the number of running days, hours and minutes no, is it possible to bring date and time too?

php mysql

asked by anonymous 29.04.2018 / 05:49

1 answer

What tools should I use to create an android application Formatting / Code line break in Android Studio

score 4 · Accepted Answer

I can give you two different ways of doing this. Remember that the comparison is done with the server time.

1st

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF(prod_data_cadastro,NOW())) / 24), ' dias ',
MOD(HOUR(TIMEDIFF(prod_data_cadastro, NOW())), 24), ' horas ',
MINUTE(TIMEDIFF(prod_data_cadastro,NOW())), ' minutos') as 'data_corrida'
FROM produto;

2nd

SELECT CONCAT(
TIMESTAMPDIFF(day,prod_data_cadastro,NOW()) , ' dias ',
MOD( TIMESTAMPDIFF(hour,prod_data_cadastro,NOW()), 24), ' horas ',
MOD( TIMESTAMPDIFF(minute,prod_data_cadastro,NOW()), 60), ' minutos '
) as 'data_corrida'
FROM produto;