PostgreSQL常用SQL

持续更新中…

--与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