CODEWITHSUNDEEP
mysqlsql
DJafari
DJafari

Reputation: 13535

Group by day from timestamp

In my posts table, it saves timestamp for each post record.

What query can group posts by day, using this timestamp column?

Upvotes: 67

Views: 105816

Answers (4)

Pankaj Yadav
Pankaj Yadav

Reputation: 37

I did a lot of research about this error and got a solution that if you are select timestamp within DATE then you have to use this symbol (``) before and after of column name.

select DATE(addedon) as addedon, count(*) from data where id= '$bid' GROUP BY DATE(addedon)

Upvotes: 0

Gerardo Lagger
Gerardo Lagger

Reputation: 641

SELECT DATE(timestamp) AS ForDate,
        COUNT(*) AS NumPosts
 FROM   user_messages
 GROUP BY DATE(timestamp)
 ORDER BY ForDate

This found for me. I have timestamp like "2013-03-27 15:46:08".

Upvotes: 52

MatBailie
MatBailie

Reputation: 86706

SELECT
    *
FROM
(
    SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate,
           ROW_NUMBER() OVER (PARTITION BY DATE(FROM_UNIXTIME(MyTimestamp)) ORDER BY MyTimeStamp) AS PostRowID,
           *
    FROM   MyPostsTable
)
    AS sequenced_daily_posts
WHERE
    ForDate = <whatever date(s) you want>
    AND PostRowID <= 2

Upvotes: 0

p.campbell
p.campbell

Reputation: 100557

How about this? Using the DATE function:

 SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate,
        COUNT(*) AS NumPosts
 FROM   MyPostsTable
 GROUP BY DATE(FROM_UNIXTIME(MyTimestamp))
 ORDER BY ForDate

This will show you the number of posts on each date that the table has data for.

Upvotes: 79

Related Questions