我有一张postgres 11的table，像这样：

CREATE TABLE schema.foo_numbers (

 id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
 created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
 quantity INTEGER,
 category TEXT
);

它有如下数据：

id |       created_at                  | quantity | category 
----+------------------------+----------+----------
  1 | 2020-01-01 12:00:00+00 |        2    | a
  2 | 2020-01-02 17:00:00+00 |        1    | b
  3 | 2020-01-01 15:00:00+00 |        6    | a
  4 | 2020-01-04 09:00:00+00 |        1    | b
  5 | 2020-01-05 19:00:00+00 |        2    | a
  6 | 2020-01-06 23:00:00+00 |        8    | b
  7 | 2020-01-07 20:00:00+00 |        1    | a
  8 | 2020-01-08 04:00:00+00 |        2    | b
  9 | 2020-01-09 23:00:00+00 |        1    | a
 10 | 2020-01-10 19:00:00+00 |        1   | b
 11 | 2020-01-11 05:00:00+00 |        1    | a
 12 | 2020-01-12 21:00:00+00 |        1   | b
 13 | 2020-01-13 01:00:00+00 |        1   | a
 14 | 2020-01-14 18:00:00+00 |        1   | b

我还有一个表跟踪foo类别的某些属性：

create table schema.foo_category_properties (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
category TEXT NOT NULL,  
some_bool BOOLEAN NOT NULL DEFAULT FALSE
);

此表包含如下数据：

id | category | some_bool 
----+----------+-----------
  1 | a        | f
  2 | b        | f

我需要创建一个postgres函数（通过postgrest api从app logic调用），对于参数$timestamp，它将返回每个类别的最新记录，其中created_在<=$timestamp。
理想情况下，传入的参数将被视为时区为“america/losïangeles”的timestamp，函数返回最新记录，其时间戳显示在同一时区——但是，如果不可能这样做，并且所有时间戳都保持在utc（在应用程序逻辑中偏移），则也可以，前提是以一致的方式返回正确的数据。
服务器时间设置为utc:

psql => show time zone; 
 TimeZone 
----------
 UTC
(1 row)

我写的postgres函数是这样的：

CREATE OR REPLACE FUNCTION schema.foo_proc (end_date TEXT)   

  RETURNS TABLE (
      id INTEGER,
      category TEXT,
      quantity BIGINT,
      snapshot_count NUMERIC,
      latest_entry TIMESTAMP WITH TIME ZONE 
  )
  AS $$
  #variable_conflict use_column

BEGIN
RETURN QUERY 

    SELECT 
    alias1.id,
    alias1.category, 
    alias1.quantity,
    alias1.snapshot_count,
    alias2.latest_entry AS latest_entry

    FROM
      (
          SELECT 
          id,
          category,
          quantity,
          sum(quantity) OVER (partition by category ORDER BY created_at) AS snapshot_count 
          FROM 
          schema.foo_numbers 
      ) AS alias1
    INNER JOIN 
    (
       SELECT
        max(id) AS id,
        category, 
        max(created_at AT TIME ZONE 'America/Los_Angeles') AS latest_entry
        from 
        schema.foo_numbers 
        WHERE created_at AT TIME ZONE 'America/Los_Angeles' <= to_timestamp($1', 'YYYY-MM-DD HH24:MI:SS') :: TIMESTAMPTZ AT TIME ZONE 'America/Los_Angeles'
        group by category
        order by category 
    ) AS alias2
    ON 
    alias1.id = alias2.id
    INNER JOIN 
    schema.foo_category_properties fcp 
    ON 
    alias2.category = fcp.category
    WHERE fcp.some_bool IS FALSE
    ORDER BY 
    alias1.category 
    ;
END;
$$ LANGUAGE plpgsql;

这是数据 foo_numbers 时间戳移到时区“美国/洛杉矶”

psql=> select id, created_at at time zone 'america/los_angeles', quantity, category  from schemai.foo_numbers order by created_at;

 id |      timezone               | quantity | category 
----+---------------------+----------+----------
  1 | 2020-01-01 04:00:00 |        2     | a
  3 | 2020-01-01 07:00:00 |        6     | a
  2 | 2020-01-02 09:00:00 |        1     | b
  4 | 2020-01-04 01:00:00 |        1     | b
  5 | 2020-01-05 11:00:00 |        2     | a
  6 | 2020-01-06 15:00:00 |        8     | b
  7 | 2020-01-07 12:00:00 |        1     | a
  8 | 2020-01-07 20:00:00 |        2     | b
  9 | 2020-01-09 15:00:00 |        1     | a
 10 | 2020-01-10 11:00:00 |        1    | b
 11 | 2020-01-10 21:00:00 |        1    | a
 12 | 2020-01-12 13:00:00 |        1    | b
 13 | 2020-01-12 17:00:00 |        1    | a
 14 | 2020-01-14 10:00:00 |        1    | b
(14 rows)

参数的预期输出： "end_date":"2020-01-07 19:00:00" 会是

id | category | quantity | snapshot_count |      latest_entry      
----+----------+----------+----------------+------------------------
  6 | b        |        8 |             10 | 2020-01-06 15:00:00
  7 | a        |        1 |             11 | 2020-01-07 12:00:00
(2 rows)

但是，同一参数的实际输出为：

id | category | quantity | snapshot_count |      latest_entry      
----+----------+----------+----------------+------------------------
  5 | a        |        2 |             10 | 2020-01-05 19:00:00+00
  6 | b        |        8 |             10 | 2020-01-06 23:00:00+00
(2 rows)

类似的意外结果也发生在utc时转换为timestamptz的参数中。
在我尝试过的所有变体中，返回的行与参数边界不匹配。
很明显，我没有理解pg中时区的处理方式——我已经详细阅读了官方文档和一些相关的问题，也在pg论坛上讨论了to_timestamp（）函数，但是经过大量的尝试和错误，仍然没有得到正确的结果。
非常感谢您的指导！