Multiply by value from related row with latest date
A LATERAL subquery would do the job:

SELECT t.staff_id, t.entry_date, t.hours * r.rate AS total_rate 
FROM   time_table t
LEFT   JOIN LATERAL (
   SELECT r.rate
   FROM   rates_table r
   WHERE  r.staff_id = t.staff_id
   AND    r.start_date &lt;= t.entry_date -- "on or before that time entry"
   ORDER  BY r.start_date DESC NULLS LAST
   LIMIT  1
   ) r ON true;


db&lt;>fiddle here

Details depend on more information. The LEFT JOIN keeps all rows from time_table in the result, even if no rate is found. (total_rate is NULL in that case.)

This is typically efficient for many rate entries per staff_id - if you have an index on rates_table(staff_id, start_date DESC NULLS LAST) or similar.

If you can get index-only scans out of it, a covering index would be better, yet:

CREATE INDEX ON rates_table (staff_id, start_date DESC NULLS LAST) INCLUDE (rate);


This form of the index requires Postgres 11 or later. See:


Can Postgres use an index-only scan for this query with joined tables?


Depending on table definition, indexes, data distribution etc. other query styles may be preferable. For querying the whole table and only few rows per staff_id, DISTINCT ON might be faster ...

Related:


Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest record per user
Slow left join lateral in subquery
What is the difference between LATERAL and a subquery in PostgreSQL?