How to get query results for a particular MONTH or YEAR from MySQL database when your time format stored on the database is unixtime? It is all lies on the query code and I will show you how with scenarios and example queries.
SCENARIO 1 - You want to select all the rows inserted in February. The insert date is recorded in unixtime in the insertDate field.
SELECT * FROM table WHERE MONTH(FROM_UNIXTIME(insertDate)) = 2
SCENARIO 2 - You want to select all the rows inserted in March but now you want to specify it by the month's full name.
SELECT * FROM table WHERE MONTHNAME(FROM_UNIXTIME(insertDate)) LIKE 'March'
SCENARIO 3 - You want to select all the rows that were inserted in April and in the year of 2007.
SELECT * FROM table WHERE MONTHNAME(FROM_UNIXTIME(insertDate)) = 4 AND YEAR(FROM_UNIXTIME(insertDate)) = 2007
These are the MySQL native functions that has been used on previous queries. Here is some basic explanations for those functions:
FROM_UNIXTIME() converts Unixtime format date to language that you could adjust to your own liking. With it you can transform an Unixtime date, such as '1270488983' into 'Monday, 5th April 2010' or '05-04-2010 17:36:22' or any other else according to format string you specified.
MONTH() return the month value of a given date string. Sadly it could not process an Unixtime value. Only the format of YYYY-MM-DD is accepted. MM means a zero must be placed before a single digit month value. The month format that is given by this function is M. For example,
MONTH('2010-04-05') will give
MONTH(). Except it returns the full name of the month. For example,
MONTHNAME('2010-04-05') will give you 'April'.
I know you now could guess what
YEAR() is for. Hehe.