timestamp by month/day

[jester626]

I have a script I am trying to write that will pull a unix timestamp from a MySQL database. I have no problem pulling the data using a WHERE starttime > ### AND endtime < ### which is good if I want to manually input a start and end time. My problem begins when i want to just pull the data out and want to simply pull the data in a GROUP By format by day. This is going to be a real simple appointment type of script. where the database has the following fields. RecID, Lastname,Firstname,AppointmentDate.

The output would be something like:

 

April 15, 2010

Appt. Time | Last Name | First Name

 

April 17, 2010

Appt. Time | Last Name | First Name

 

and so on.

 

Any help would be appreciated.

 

Jester

 

 

[Ken2k7]

I think you want ORDER BY as well. Then it's up to you on figuring out how to display it the way you want it.

[PFMaBiSmAd]

You have stumbled upon yet another of the shortcomings of a Unix Timestamp. For it to be usable in the way that humans treat date/time information, it must be converted into a human date/time format.

 

You can use the mysql FROM_UNIXTIME() function to get a usable DATE or DATETIME value, but that will slow down every query that uses it. I would recommend switching to a DATE or DATETIME data type. You can do a one time conversion of your existing values into a DATE or DATETIME value.