获取1970-01-01到当前时区时间的时间戳

根据时间戳转化成格式化时间函数dateadd()，根据表dm_config 中属性param_name='TIMEZONE' 配置时区偏移数，如保存的是服务器时区0时区，需要显示上海(+8)时区，则在这里配置8。

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;

获取当前时间戳(当前服务器所在时区) 函数：datediff()

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;

 当前时间函数getdate()。例：2020-11-26 00:50:04.688341

 SELECT LOCALTIMESTAMP as result; 

 

sql查询获取

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

查看条件时间格式化

select dateadd('ss', 1606238331, '1970-01-01'); -->2020-11-24 17:18:51

查看本地时间(电脑所设时区时间)getdate()。

 SELECT LOCALTIMESTAMP as result; -->2020-11-26 00:50:04.688341