Calculate difference between timestamp field with specific timestamp

Navigation
0

I'm trying to develop a ranking table for a sort of questionnaire.

Each day a question is asked at 4:00 p.m., which can be answered by 17:59:59 the next day. The table has to show the position of the participants taking into account the number of correct answers and the time.

In other words, it responds right and faster it comes first. The number of questions answered also counts, if arch does not respond one day is penalized.

My table will be of the sort: 

+-------+---------+---------------------+
|userid | acertou |      timestamp      |
+-------+---------+---------------------+
|   2   |    1    | 2018-02-07 16:00:01 |
|   1   |    1    | 2018-02-07 16:02:00 |
|   3   |    1    | 2018-02-07 17:00:00 |
|   1   |    0    | 2018-02-08 16:00:02 |
|   3   |    1    | 2018-02-08 16:00:05 |
|   2   |    0    | 2018-02-08 16:01:00 |
+-------+---------+---------------------+

I've already started with this query: 

SELECT 'userid', 'acertou', 'timestamp', 
count(acertou) as cont 
FROM 'resultados' 
WHERE acertou = 1
GROUP BY 'userid '
ORDER BY cont DESC, timestamp DESC

But I realized that this is not what I want because the ranking has to be cumulative but taking into account the several days.

Does anyone have an idea how I can do this?

    
asked by anonymous 08.02.2018 / 13:42

1 answer

1

According to the chat, your problem is that you do not have a reference to the start date of the questions, so you can not calculate the difference between the dates.

Having this table with the dates and questions, the select would be basically this way: 

-- soma acertos, e verifica a diferença em horas, minutos, segundos
SELECT userid, SUM(acertou),
-- Diferença em horas
SUM(TIMESTAMPDIFF(HOUR,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) horas,
-- Diferença em minutos
SUM(TIMESTAMPDIFF(MINUTE,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) minutos,
-- Diferença em segundos
SUM(TIMESTAMPDIFF(SECOND,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) segundos
FROM resultados
-- agrupa por usuário
GROUP BY userid
-- OPICIONAL, verifica se o usuário respondeu todas as perguntas
HAVING SUM(acert) = (SELECT COUNT(*) FROM (SELECT DISTINCT(perg_n) FROM resultados) qtd_perguntas)
-- ordena pelo que tem mais acertos, depois pelos tempos ( quanto mais tempo sobrou, menos tempo usou.
ORDER BY acertos DESC, horas DESC, minutos DESC, segundos DESC;
    
08.02.2018 / 13:54
anyone know why this error? Full Menu in Jquery