How to group records per day using Unix Timestamp?

Asked

Viewed 52 times

1

I would like to group the results by days that belong to the period defined in the query.

SELECT count(*) FROM historico WHERE contato_id IN (19, 45) AND createdAt BETWEEN 1556668800 AND 155936879;

What would be the best solution for this case?

mysql

1 answer

2


Assuming that the field createdAt represents a time in Unix. If you create a day-formatted column, maybe you can use it to group the records.

SELECT DATE_FORMAT(FROM_UNIXTIME(createdAt), '%d/%m/%Y') AS dia,
       COUNT(*) AS total
FROM   historico 
WHERE  contato_id IN ( 19, 45 ) 
       AND createdAt >= 1556668800 
       AND createdAt <= 155936879 
GROUP  BY dia

Edit 1
If your field does not represent a time in Unix, you can try this way:

SELECT DATE_FORMAT(createdAt, '%d/%m/%Y') AS dia,
       COUNT(*) AS total
FROM   historico 
WHERE  contato_id IN ( 19, 45 ) 
GROUP  BY dia

See this fiddle that illustrates the above query working. Note that there are 2 Inserts with the same date and one that does not fall into the range IN (19, 45)

  • @Victorcarnaval I edited the answer with a new possibility and online example.

  • Thanks for the answer, solved my problem.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.