Menu
I have the following sql table (simplified)
ID (int) title (text) date (int)
--------------------------------------------
1 Hello World 1378148920
2 Hello World2 1378182183
3 Hello World3 1378129838
4 Hello World4 1378146160
5 Hello World5 1378138038
....
The table has thousands of entries.
I wan't to ask you if it is possible to build a SQL query that groups all the posts by date but only as day.
So at the end I wan't to build a graph like this (for the last 5 days):
02.09.2013: 13 posts
01.09.2013: 14 posts
NOTE: the timestamp aren't real (they are all from today)
261
In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
Unix time is the number of seconds that have elapsed since 00:00:00 UTC on 1 January 1970, excluding leap seconds. This time is named the Unix epoch, because it is the start of the Unix time.
You can use from-unixtime()
select FROM_UNIXTIME(`date`, '%d.%m.%Y') as ndate,
count(id) as post_count
from your_table
group by ndate
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
