sqlite : query additional columns with compound select

I have a table containing this columns :

| name | city | time | version |

I use this query to find all rows with new adresses between 2 versions :

select name, city from adresses where version = 2 
except 
select name, city from adresses where version = 1

It's working but i would like to retrieve also time value but time value change between each version.

I would like something like that :

NOT WORKING
select time from adresses where ve开发者_如何学JAVArsion = 2 and 
(select name, city from adresses where version = 1 
except select name, city from adresses where version = 2);

Someone know how i can do that ?

Thanks

Not sure I'm understanding your question correctly, but based on the query you wrote, I think this is what you actually wanted:

SELECT time FROM adresses a
INNER JOIN (
   select name, city from adresses where version = 1 
   except 
   select name, city from adresses where version = 2
) new ON new.name = a.name AND new.city = a.city
WHERE version = 2

If you are looking for the time difference between two versions of a record (which is what you also mention in your question) then you could do something like this:

SELECT a2.*, a2.time - a1.time as time_diff
FROM adresses a1
INNER JOIN adresses a2 ON a1.name = a2.name AND a1.city = a2.city
WHERE a1.version = 1 AND a2.version = 2