[RESOLVED] FROM_UNIXTIME problem

andynightingale

On my web page I have a repeat region with the date picked up from 'flights.depart_time' The only problem is that the db column contains both out depart time and inbound depart time, and due to the grouping doesn't seem to differentiate between the two. I only want to show the first flight time (not the return). Some are correct but some show the later date.

I tried MIN(FROM_UNIXTIME(flights.depart_time,"%d")) but of course that only worked if the first date was less than the second. If the out flight was on the 31st and the return flight was the following month on the 2nd it uses the smaller value. And same for month ie Dec > Jan. It picks 1 over 12

I need a way in SQL of finding the smaller value from "FROM_UNIXTIME" before I split it into day, month and year.

Any ideas?

'SELECT tour.*, 
	performances.performance_id, 
	venues.venue_id,
	FROM_UNIXTIME(flights.depart_time,"%d") AS day, 
	FROM_UNIXTIME(flights.depart_time,"%m") AS month,
	FROM_UNIXTIME(flights.depart_time,"%Y") AS year
	FROM tour 
	LEFT JOIN performances ON performances.tour_id = tour.tour_id
	LEFT JOIN venues on performances.venue_id = venues.venue_id
	LEFT JOIN flights on flights.tour_id = tour.tour_id
	WHERE tour.tour_type LIKE("%'.$type.'%") AND venues.venue_location = "'.$location.'" 
	GROUP BY tour.tour_id, venues.venue_location
	ORDER BY flights.depart_time ASC';

konsu

hello, did you try to omit the format parameter from FROM_UNIXTIME() when calculating MIN()?

andynightingale

Duh :-)
so obvious I missed it.... and then use php to split the date. thank you

andynightingale

MIN(FROM_UNIXTIME(flights.depart_time)) AS day

works fine but gives me the full date and time

2005-10-01 08:30:00

How do I get this into 01/01/2005 format?

My present code is

<?php echo date($data['day'])?>

konsu

did you try FROM_UNIXTIME(MIN(flights.depart_time),format) where 'format' is the same as in DATE_FORMAT function (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html)?