Editing a post in Selecting Distinct Months From Unix_timestamp

[Kristian]

Hi - I have a problem that hopefully can be easily resolved.

I have an Events table in MYSQL with 'start_date' and 'end_date' fields. Data in these fields are in UNIX_TIMESTAMP format.

I wanted to create a dropdown list showing DISTINCT months and/or years from all records using the 'start_date' data.

Drop down might look like this:
Jan - 2003 --- Might be 5 records in here
Mar - 2003 --- Might be 2 records in here
Oct - 2003 --- Might be 23 records in here
Feb - 2004 --- Might be 1 record in here

So, any ideas how I select distinct months and/or years from a UNIX_TIMESTAMP stored in MYSQL table???

I have this so far...

<?php
////////////////////////////////////////////////////////////////////
// Dynamic drop down list for Months with Events //
////////////////////////////////////////////////////////////////////
?>
<select name="event_month" class="forms-inputbox" id="event_month">
<?php
$query = "SELECT DISTINCT start_date FROM events ORDER BY start_date";
$result = mysql_query($query, $link);
if(mysql_num_rows($result)) {
while($row = mysql_fetch_row($result))
{
$monthyear[0] = date("M - Y",$row[0]);
print("<option value=\"$row[0]\">$monthyear[0]</option>\n");
}
} else {
print("<option value=\"\">Error</option>");
}
?>
</select>

This is what I get...

http://www.flexifoil.com/images/outcome_1.gif

...there are 8 events in August and so August - 2003 is repeated 8 times...so any idea how I can show only DISTINCT dates...?

[stuka]

I'm pretty sure you'll either need to alter your schema to support month/day/year fields, or create a temp table that has those fields, and then do a select on that temp table with distinct months. If I'm guessing at your direction from there, the temp table would make it easy to pull out the desired months' events after the option was chosen, so it wouldn't be too much of an expense IMO.

[DNAunion2000]

/*DNAunion*/ I am guessing the problem is that the DISTINCT restriction is being applied to a timestamp, not just to a date. A timestamp has a date, hours, minutes, seconds, etc. So if all 8 events occurred on the same day at different times, then DISTINCT is correctly returning all 8 distinict values, as it should.

You should be able to add a date function to the SQL SELECT to extract just the date, leaving off the other stuff (hours, minute, second, etc.), and it should come out correctly then. Is it DATEPART()??? Unfortunately, I don't have my SQL Server books here with me so I can't show you the exact syntax.

[gish]

using DISTINCT, will return EVERY record where there are any differences in any field. Is there an ID field?...if so then this field is different, and will be selected.

[DNAunion2000]

/*DNAunion*/ Got one of my SQL Server books. I think the following will work.

SELECT DISTINCT CONVERT(CHAR(10), start_date, 101) AS startdate FROM events ORDER BY startdate

The CONVERT function converts the datetime datatype to a char(10) expression in the form "mm/dd/yyyy" (the 101 argument is what specifies the "mm/dd/yyyy" format). The DISTINCT keyword will then ensure that only one instance of any duplicates (example, "08/01/2003" and "08/01/2003") will be retained.


PS: I had originally stated that the DATEPART() function is aht you probably needed. But from what my book shows, DATEPART() will do just about anything to a datetime value except return just the date part. For a date/datetime value, it can return the day of the month, the day of the year, the hour, the millisecond, the minute, the month, the quarter, the second, the week, the weekday, and the year: but it apparently won't lop off everything to the right of the day (the hours, minutes, seconds, and milliseconds) to return just the date.