我的键集分页功能遇到了一个问题,因为我接收的时间戳值被限制为毫秒,导致与数据库期望的精度不匹配.这种限制源于JavaScript Date实现中固有的精度不足.不幸的是,我无法控制这个方面,因为这个值是从API端点获得的.虽然一种解决方法涉及向客户端传输bigint字符串值以及从客户端传输bigint字符串值,但在诉诸可能耗时的更改之前,我对探索这个问题的替代解决方案感兴趣.
Schema (PostgreSQL v15)个
CREATE TABLE IF NOT EXISTS public.activities
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
created_at timestamp with time zone DEFAULT now(),
PRIMARY KEY (id)
);
CREATE INDEX activities_created_at_id ON activities (created_at, id);
Ordering
下面是按created_at asc
,id asc
排序的表内容
select id, created_at from activities order by created_at asc, id asc;
id | created_at |
---|---|
e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474123Z |
ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474123Z |
0cf727a8-5efc-454b-ba1b-ea301e2b1a82 | 2024-03-14T07:23:56.474222Z |
10f1dd9b-2cd8-40bb-a199-2d9c922d07b1 | 2024-03-14T07:23:56.474222Z |
3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe | 2024-03-14T07:23:56.474222Z |
ac31e591-d4ea-4ef5-956a-c07bd043d9ea | 2024-03-14T07:23:56.474222Z |
b9b33ca5-2cd1-490b-a6be-d28784f75b2a | 2024-03-14T07:23:56.474222Z |
bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474222Z |
c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474222Z |
ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474222Z |
Issue with Significant Digits
与数据库中存储的数据相比,提供的输入缺乏精确性.由于JavaScript在支持超过毫秒的精度方面的限制,以下查询检索所有活动:
select id, created_at from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;
id | created_at |
---|---|
e34d5557-43d7-4f81-802b-791c213ea5cb | 2024-03-14T07:23:56.474Z |
ea7e9cad-89f2-4610-898f-62a04e8b5331 | 2024-03-14T07:23:56.474Z |
0cf727a8-5efc-454b-ba1b-ea301e2b1a82 | 2024-03-14T07:23:56.474Z |
10f1dd9b-2cd8-40bb-a199-2d9c922d07b1 | 2024-03-14T07:23:56.474Z |
3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe | 2024-03-14T07:23:56.474Z |
ac31e591-d4ea-4ef5-956a-c07bd043d9ea | 2024-03-14T07:23:56.474Z |
b9b33ca5-2cd1-490b-a6be-d28784f75b2a | 2024-03-14T07:23:56.474Z |
bc9763d0-6d24-4fbf-bc23-7eff1589280a | 2024-03-14T07:23:56.474Z |
c442f6ea-61bf-42d4-aa85-4eab1d95f569 | 2024-03-14T07:23:56.474Z |
ded826f7-65f0-4d23-9366-049823ba49ec | 2024-03-14T07:23:56.474Z |
Using 100 to Cap Precision When Querying
在达到预期结果的同时,我不太愿意只在查询中处理这个问题.这种方法需要根据字段类型动态调整查询构造,这可能会带来性能方面的问题:
select id, created_at from activities
where (date_trunc('milliseconds', created_at::timestamptz), id) < (date_trunc('milliseconds', '2024-03-14T07:23:56.474Z'::timestamptz), 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at desc, id desc;
id | created_at |
---|---|
ac31e591-d4ea-4ef5-956a-c07bd043d9ea | 2024-03-14T07:23:56.474Z |
3c00c38e-45b9-4c3c-9d05-fc1ca2177dbe | 2024-03-14T07:23:56.474Z |
10f1dd9b-2cd8-40bb-a199-2d9c922d07b1 | 2024-03-14T07:23:56.474Z |
0cf727a8-5efc-454b-ba1b-ea301e2b1a82 | 2024-03-14T07:23:56.474Z |
考虑到这些选项,我愿意听取社区的建议和见解,以有效地解决这个分页挑战.