Programmers Block: List dates from unix timestamps w/o murdering the server.

January 24, 2007 5:45 AM

Programmers block: I have a table in my database. It has events that occurred and the unix time stamp (seconds since the unix epoch) stored in it. I can't decide on the most efficient way (or even if there is one) to list the dates on which events have been recorded.

Basically, I would like to create an overview, where I can view a list of days when something has occurred. The "best" solution I can think of involves pulling everything, parsing each time stamp to check the date, add the unique dates to an array and then return that out for display. That's just not an option as this is going to be a very large table.

I can think of a couple dirty ways of achieving this, but I can't shake the feeling that I'm overlooking a solution. I might just be dumb.

I'm using PHP and MYSQL here, but I can translate a concept into code, so don't let unfamiliarity prevent you from responding. I can

And thank you very much for any help.

posted by jcruden to Computers & Internet (3 answers total)

Best answer: SELECT DISTINCT FROM_UNIXTIME(your_timestamp_column, '%m-%d-%Y') FROM your_table ought to do it, I think.
posted by uncleozzy at 5:55 AM on January 24, 2007

Best answer: Check out the FROM_UNIXTIME function in MySQL:

mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'


What other information are you trying to pull in addition to the date? I have a feeling you can do this all with a SELECT statement and then just display your recordset with PHP.
posted by jbiz at 5:55 AM on January 24, 2007 [1 favorite]

Response by poster: Wow - that is perfect. Thank you very much.

Man, I knew there had to be something I was overlooking, and that was it.
posted by jcruden at 6:00 AM on January 24, 2007