根据表中配置的时区偏移，获取对应的时间戳

DECLARE
p_Interval ALIAS FOR $1;
p_N ALIAS FOR $2;
p_Date ALIAS FOR $3;
v_timezone numeric;


BEGIN
 if exists (select 0 from pg_tables where tablename='dm_config') then
   select param_value into v_timezone from dm_config where param_name='TIMEZONE';
      if not found then 
      INSERT INTO dm_config(param_name,param_value) VALUES('TIMEZONE','0');
      end if;
   select param_value into v_timezone from dm_config where param_name='TIMEZONE';
   if (p_Interval = 's' or p_Interval='ss')  then
   return p_Date + p_N*interval'1 second'+v_timezone*3600*interval'1 second';
      elseif p_Interval = 'm' then
      return p_Date + cast(p_N || ' months' as interval);
        elseif p_Interval = 'y' then
        return p_Date + cast(p_N || ' years' as interval);
           else
           raise exception 'dateadd interval parameter not supported';
           return null;
    end if;
 else
   if p_Interval = 's' or p_Interval='ss'  then
   return p_Date + p_N*interval'1 second';
    elseif p_Interval = 'm' then
    return p_Date + cast(p_N || ' months' as interval);
      elseif p_Interval = 'y' then
      return p_Date + cast(p_N || ' years' as interval);
        else
        raise exception 'dateadd interval parameter not supported';
        return null;
   end if;
end if;
END;

获取当前时间戳

DECLARE
arg_mode alias for $1;
arg_d2 alias for $2;
arg_d1 alias for $3; 

BEGIN
if arg_mode = 'ss' or arg_mode = 's' then
return cast(date_part('epoch',(arg_d1-arg_d2)::interval) as integer);
elsif arg_mode = 'dd' or arg_mode = 'd' or arg_mode = 'y' or arg_mode =
'dy' or arg_mode = 'w' then
return cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = 'ww' then
return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = 'mm' OR arg_mode = 'm' then
return 12 * (date_part('year',arg_d1) - date_part('year',arg_d2))
+ date_part('month',arg_d1) - date_part('month',arg_d2)
+ case when date_part('day',arg_d1) >
date_part('day',arg_d2)
then 0
when date_part('day',arg_d1) =
date_part('day',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time)
then 0
else -1
end;
elsif arg_mode = 'yy' OR arg_mode = 'y' OR arg_mode = 'yyyy' then
return (cast(arg_d1 as date) - cast(arg_d2 as date)) / 365;
end if;

END;

 

sql查询获取

select datediff('s','1970-1-1',getdate()) as current_time_diff

查看当前时间位于什么时区

select dateadd('ss', 1606238331, '1970-01-01');

查看本地时间getdate()

 SELECT LOCALTIMESTAMP as result;