Fun with time

-- Convenience Functions
CREATE OR REPLACE FUNCTION to_epoch(IN timestamptz, OUT epoch float8) AS 
  $$ SELECT extract(epoch from $1) $$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION ts_at_tod(IN timestamptz, IN timetz, OUT timestamptz) AS 
  $$ SELECT date($1) + $2 $$ LANGUAGE SQL;
  
-- Get list of target time between start and end
CREATE OR REPLACE FUNCTION times_between(IN range_start timestamptz, IN range_end timestamptz, IN t timetz) 
  RETURNS setof timestamptz AS $$
    SELECT * FROM
      generate_series(
        ts_at_tod($1,$3),
        ts_at_tod($2,$3),
        '1 day'
        ) g
      WHERE g.g BETWEEN $1 AND $2;
  $$ LANGUAGE SQL;
 
-- this function will return a set of any points along a line interpolated at the given time of day 
CREATE OR REPLACE FUNCTION m_at_tod(IN geometry, IN timetz) RETURNS SETOF geometry AS $$
  SELECT 
    (st_dump(st_locatealong(
      $1,
      to_epoch(times_between(
          to_timestamp(st_m(st_startpoint($1))),
          to_timestamp(st_m(st_endpoint($1))),
          $2
        ))
    ))).geom
;  
$$ LANGUAGE SQL;


-- create dummy table to test function with
CREATE TEMP TABLE test (id serial primary key, geom geometry);
  INSERT INTO test (geom) VALUES
    ('LINESTRING(0 0, 1000 1000)'::geometry),
    ('LINESTRING(10000 10000, 20000 20000)'::geometry)
  ;
  UPDATE test SET 
    geom=st_addmeasure(
      geom,
      to_epoch('2000-01-01 23:00'::timestamptz),
      to_epoch('2000-01-05 11:00'::timestamptz)
     ) WHERE id = 1;
  UPDATE test SET 
    geom=st_addmeasure(
      geom,
      to_epoch('2000-01-07 23:00'::timestamptz),
      to_epoch('2000-01-12 11:00'::timestamptz)
     ) WHERE id = 2;

WITH t1 AS (
  SELECT m_at_tod(geom,'13:00'::timetz) as g FROM test
) SELECT st_x(g), st_y(g), to_timestamp(st_m(g)) FROM t1;