[RESOLVED] problem with dates

[OniLink]

Hi,

I used this same query on a database that stored dates as YYYY-MM-DD and it worked fine. I'm now trying to use it on a database that uses epoch and it doesn't work.

The year and month values are 0. When they should be 2006 and 12 respectively.

Here is the query I'm using:

SELECT COUNT(*), YEAR(start_date) AS year, MONTH(start_date) AS month FROM ibf_topics WHERE (forum_id = "59" OR forum_id = "3" OR forum_id = "61") AND pinned = "0" GROUP BY year, month ORDER BY year DESC, month DESC

Thanks for any help,
~Oni.

[NogDog]

What DBMS are you using?

What type of column is start_date?

[OniLink]

I am using MySQL. Ahh, it's an int. Is there any way around this?

[bradgrafelman]

Sure - have MySQL convert from Unix timestamp to whatever you'd like:

SELECT COUNT(*), FROM_UNIXTIME(start_date, '%Y') AS year, FROM_UNIXTIME(start_date, '%c') AS month ...

For more information, see the MySQL docs on FROM_UNIXTIME().

[NogDog]

Assuming it's a UNIX timestamp, you could do:

SELECT YEAR(FROM_UNIXTIME(start_date)) AS year, ...etc....

[OniLink]

Thanks guys!