持续更新中…
--与UTC的时区偏移,以秒记
select cast(extract(timezone from now()) as bigint) as timezone;
select extract(epoch from '1970-1-1'::timestamptz);
--时间精确至天
with cte as(
select cast(extract(epoch from now()) as bigint) as time
)select cast(to_timestamp(time/86400*86400) as date) from cte;
--时间精确至小时
with cte as(
select cast(extract(epoch from now()) as bigint) as time
)select to_timestamp(time/3600*3600) from cte;
--数据库里怎么生成流水号(“XZ”+当前年+000001)
select format('XZ%s%s',extract(year from now()),to_char(nextval(pg_get_serial_sequence('public.表名','表中的serial或bigserial类型的字段名')),'FM00000'))
--行转列,行数据用逗号分隔
select array_to_string(field,',') from (select array_agg(id order by id) as field from generate_series(1,100) as id) as result