I am storing data in a mysql database with unix time()
I want to select data out for days, and echo it out like this:
Monday - value
Tuesday - value
Wednesday - value
Thursday - value
Friday - value
Saturday - value
Sunday - value
value is saved in the database as value the times are saved in the database as time
So basically I want to select time from the database in a query that groups all of them as a day, Monday.. do you get what I mean?
Any help with this would be greatly appreciated.
First, replace that integer with a real DATETIME column, then use MySQL's built-in functions to easily GROUP BY the DAYOFWEEK(column)
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
There is no advantage to storing an integer instead of a real date. Not only can you easily convert to a UNIX timestamp if you need that in your application, but you'd save storage space too.
To convert to MYSQL native date, use FROM_UNIXTIME(). DAYNAME will return the day of the week.
SELECT DAYNAME(FROM_UNIXTIME(col))
For example:
SELECT DAYNAME(FROM_UNIXTIME(1196440219))
Further, to group by you could use something like this:
SELECT DAYNAME(FROM_UNIXTIME(col)), ... FROM table GROUP BY DAYNAME(FROM_UNIXTIME(col))
