Unixtime & mysql

Question

Hi guys, I have a field on my table which is called X, and I store unixtime(php -> time()). My question is:

How can I list all the months from my DB, something like: 6.2009 8.2009 etc..

And my second question is:

How can I make a query to list all the informations based on a month and year on the same field X(i store unixtime), I know this doesn't work, but mabe you can understand it better: where date = '06.2009'

Any solutions?

Answer 1

• List all months:

  SELECT DISTINCT EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(X)) FROM MyTable;
  

• Return all rows with a given year & month:

  SELECT * FROM MyTable
  WHERE EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(X)) = 200906;
  

Answer 2

To answer your first question, you would do this with grouping:

SELECT FROM_UNIXTIME(timestamp_column, '%c.%Y') AS month, some_other_column FROM table_name GROUP BY month;

As for your second question, this depends on what you're trying to do. For example:

SELECT AVG(payment), SUM(*), FROM_UNIXTIME(timestamp_column, '%c.%Y') AS month, some_other_column FROM table_name WHERE timestamp_column BETWEEN UNIX_TIMESTAMP(200906) AND UNIX_TIMESTAMP(200907) - 1 GROUP BY month;

Would return the average of the payments and the number (sum) of rows for each group.

To get information ungrouped from a specific timeframe, reduce the query like so:

SELECT payment, some_other_column FROM table_name WHERE timestamp_column BETWEEN UNIX_TIMESTAMP(200906) AND UNIX_TIMESTAMP(200907) - 1;

Answer 3

Try this

1. SELECT FROM_UNIXTIME(X,'%m.%Y') FROM TABLE GROUP BY FROM_UNIXTIME(X,'%m.%Y')

2. SELECT * FROM TABLE WHERE FROM_UNIXTIME(X,'%m.%Y')='06.2009'

Bye