https://github.com/kufu/activerecord-bitemporal/blob/master/README.md を見て書いた

hoge.sql

CREATE TEMP TABLE "employees" (
  "emp_code" text,
  "name" text,
  "valid_from" timestamp
);

INSERT INTO "employees" VALUES
  ('001', 'Jane', '2019-01-10'),
  ('001', 'Tom', '2019-01-15'),
  ('001', 'Kevin', '2019-01-20');

WITH "employees" AS (
       SELECT "emp_code"
            , "name"
            , TSRANGE(
                "valid_from",
                LEAD(valid_from) OVER(PARTITION BY "emp_code" ORDER BY "valid_from")
              ) AS "valid_period"
         FROM "employees")
SELECT * FROM employees
;
-- emp_code | name  |                    valid_period
------------+-------+-----------------------------------------------
-- 001      | Jane  | ["2019-01-10 00:00:00","2019-01-15 00:00:00")
-- 001      | Tom   | ["2019-01-15 00:00:00","2019-01-20 00:00:00")
-- 001      | Kevin | ["2019-01-20 00:00:00",)

WITH "employees" AS (
       SELECT "emp_code"
            , "name"
            , TSRANGE(
                "valid_from",
                LEAD(valid_from) OVER(PARTITION BY "emp_code" ORDER BY "valid_from")
              ) AS "valid_period"
         FROM "employees")
SELECT *
  FROM "employees"
 WHERE '2019-01-18'::timestamp <@ "valid_period"
;
-- emp_code | name |                 valid_period
------------+------+-----------------------------------------------
-- 001      | Tom  | ["2019-01-15 00:00:00","2019-01-20 00:00:00")