find nearest record (before or after) to a given date/timestamp

Question

I am wondering how to leverage postgresql's timestamp data type to calculate some statistics. For example, say I want to calculate the profit/loss after each transaction so I have a three column table tmp:

create table tmp
(date     timestamp,
 customer text,
 price    numeric)

Sample view:

date                 | customer | price 
2014-03-17 18:23:51  | buyer    | 100
2014-03-17 18:14:24  | buyer    | 101   
2014-03-17 18:09:14  | seller   | 102
2014-03-17 18:03:52  | buyer    | 103
2014-03-17 17:57:51  | seller   | 104
2014-03-17 17:52:43  | seller   | 105
2014-03-17 17:52:36  | buyer    | 106
2014-03-17 17:52:35  | seller   | 107
2014-03-17 17:52:35  | buyer    | 108

Now the goal is to find the spread between the price paid by the buyer and the price paid by the seller based on the closest transaction of the opposite type (buy or sell) either before or after the record in question.

For example, looking at transaction 5 from the top:

2014-03-17 17:57:51  | seller   | 104 

the record where customer = 'buyer' immediately proceeding it is 00:06:01 after and the record where customer = 'buyer' preceding it is 00:05:15 before so here we would want to difference the price 104 - 106 = 2. I've searched around and haven't been able to find any hints. Thanks.

edit: in the above example it uses the price before but I am trying to find a generalized solution that will decided which nearest price, before or after, to use in a calculation.

Answer 1

SQL Fiddle

select distinct on (t1.date, t1.customer)
    t1.date, t1.customer, t1.price,
    t1.price - t2.price as price_diff,
    abs(extract(epoch from (t1.date - t2.date))) as seconds_diff 
from
    tmp t1
    inner join
    tmp t2 on t1.customer != t2.customer
order by t1.date desc, t1.customer, seconds_diff