How can I select records which are between 2 dates based on a date in a field in PostgresSQL? I have two tables:


1 with an Unique ID with the first time they visited (in date format) 
and a historical addresses table with the same Unique ID, a start field (date field), an end field (date field) and the addresses fields.


What I like to do is create a table which get the addresses of the persons (Unique ID) based on the date where they lived at the time of the visit.

I'm able to select records with my own made up dates, but I would like to get them from the 'first time visit' field. 

The SQL query with random dates:

SELECT * FROM historical_adresses
WHERE (startdate, enddate) OVERLAPS ('2013-05-15'::DATE, '2013-05-15'::DATE);


Who can help me do this in PostgresSQL?
If I understand your problem correctly, you have to join the other table to historical_adresses based on the unique ID and that the visit time falls between startdate and enddate.  

If this is so, the query could look like:

SELECT [address_elements]
FROM historical_adresses AS ha
JOIN visits AS v 
    ON ha.user_id = v.user_id
    AND v.visit_time BETWEEN ha.startdate AND ha.enddate
WHERE [find the visit somehow that you are interested in];


Note: you usually don't need all columns of a table (and definitely not from a join), so use the actual column names rather than *.