--確定日期最大值和每天序列號最大值
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 cteselect ((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 thennextval('public.test_serial') else setval('public.test_serial',((cur_date<<31) | 1))end) 
from cte
--驗證
select (3652034745753141255>>31)&4294967295,(3652034745753141255)&2147483647