Roughly, you want something like
select min_trans_time, min_trans.id as min_trans_time_id,
max_trans_time, max_trans.id as max_trans_time_id
max(trans_time) as max_trans_time,
min(trans_time) as min_trans_time,
group by trans_date) min_max_trans,
trans as min_trans,
trans as max_trans
where min_trans.trans_time = min_max_trans.min_trans_time
and max_trans.trans_time = min_max_trans.max_trans_time
Which might not be stable if several
transes share the same
trans_time (that is, running the query on the same set of data might yield different results. One easy way of solving this, if it's a concern, is to pick the max or min id, for instance. Of course, this might bias the results :).
You can also use Window Functions if you are using PostgreSQL 8.4 or later; they will provide a clearer query (clear if you are familiar with window functions :), and they let you do stuff which is pretty hard to do with the above strategy (for instance, getting the 2nd-higher value instead of the maximum). However, in my short experience, they performed worse than the self-join approach I propose here. Erwin's answer contains a version of the query using window functions. It would be interesting to note which query executes better and/or can be optimized better (by adding indexes, etc.).