--确定日期最大值和每天序列号最大值
with cte as(
select (((1::bigint)<<32)-1) as max_date_second,(((1::bigint)<<31)-1) as max_sn
)select
max_date_second,
to_timestamp(max_date_second),
max_sn,
((max_date_second<<31)|max_sn) as max_val,
(((max_date_second<<31)|max_sn)>>31)&4294967295 as restor_max_date_second,
(((max_date_second<<31)|max_sn))&2147483647 as restor_max_sn
from cte
select ((1149304832::bigint)>>31)&4294967295,
--创建序列,序列中的内容包含日期和顺序号
drop SEQUENCE if exists test_serial;
select (((extract(epoch From current_date))::bigint)<<31) + 1;
CREATE SEQUENCE test_serial START 3652034743605657601;
select nextval('public.test_serial');
--序列第二天重新从1开始计数
with cte as(
select
1700611201::bigint as cur_date, --表示2023-11-23
--((extract(epoch From current_date))::bigint) as cur_date, --表示2023-11-22
((currval('public.test_serial')>>31)&4294967295) as old_date
) select
(case when cur_date=old_date then
nextval('public.test_serial')
else
setval('public.test_serial',((cur_date<<31) | 1))
end)
from cte
--验证
select (3652034745753141255>>31)&4294967295,(3652034745753141255)&2147483647
07-27