Mysql Query for breaking unix time stamp into periods

I have a database table named posts in which there are 3 fields like :

• post_id (int, autoincrement, primary)
• post_text (var开发者_运维技巧char 200)
• post_time (int)

I am using the following mysql query to insert data into the above mentioned table :

INSERT INTO `posts` 
  (`post_id`, `post_text`, `post_time`) 
VALUES 
  (NULL, '324324234', UNIX_TIMESTAMP(NOW()))         

This works perfectly fine.

Now I am trying to retrieve the data based on weeks from the above table. Say, I want to display the number of posts inserted in database every week (Mon - Sun). I know I can do it via YearWeek function in mysql but it's not working properly.

You stored the date/time as a unix timestamp, so you need to convert it into a DATETIME before you can use YEARWEEK - use:

  SELECT YEARWEEK(FROM_UNIXTIME(p.post_time)),
         COUNT(*) AS numPosts
    FROM POSTS p
GROUP BY YEARWEEK(FROM_UNIXTIME(p.post_time))

DATE_FORMAT would provide the ability to customize the year/week value.

Reference:

• FROM_UNIXTIME
• YEARWEEK
• DATE_FORMAT

@OMGPonies --

SELECT YEARWEEK(FROM_UNIXTIME(p.post_time)) AS yw,
     COUNT(*) AS numPosts
FROM POSTS p
GROUP BY yw;

will run faster (probably -- certainly no slower).