0

Personal I have this query that is returning the time in minutes for me. But I want this query to return in hours and minutes rather than in minutes. It's already tried using FLOOR() function, but to no avail.

SQL

SELECT
   vencido.tickets_id,
   Tickets.name,
   Tickets.due_date,
   Tickets.solvedate,
   Cat.completename,
   TIMESTAMPDIFF(hour, Tickets.date, Tickets.solvedate) as Tempo 
FROM
   glpi_vencidos as vencido 
   INNER JOIN
      glpi_tickets as Tickets 
      ON (Tickets.id = vencido.tickets_id) 
   INNER JOIN
      glpi_itilcategories as Cat 
      ON (Tickets.itilcategories_id = Cat.id) 
WHERE
   Tickets.solvedate between STR_TO_DATE('01/06/2017', '%d/%m/%Y') and STR_TO_DATE('01/07/2017', '%d/%m/%Y') 
   and vencido.situacao = 1

My result in minutes

My result

3

You need DIV and % operators.

DIV is the integer division and % is the modulo function, returning the reminder:

SELECT 12345 DIV 60 AS hrs, 12345 % 60 as min;

UPDATE

You have to change your query such way:

SELECT
   . . . . .
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) DIV 60 AS TempoH,    
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) % 60 AS TempoM
   . . . . . 
  • What would the implementation look like in my original code? – Renato Lazaro Jul 12 '17 at 0:20
  • 1
    Can you post your query that returns the time in minutes? – Kondybas Jul 12 '17 at 0:25
  • This is my question, but I can pass you here as well.. Https://gist.github.com/Nerato/d5b46ee72981f5e9bd32f60bfed8412b – Renato Lazaro Jul 12 '17 at 0:29
  • I'll test here and tell you the result! Do you think my query can be improved? – Renato Lazaro Jul 12 '17 at 0:41
  • JOIN, CROSS JOIN and INNER JOIN are completely equivalent in the mysql so you can replace INNER JOIN by plain JOIN. – Kondybas Jul 12 '17 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.