Shell和SQL的1970-01-01间隔秒数计算

date命令对时间纪元的计算

date -d@'0' +%F_%T
# 1970-01-01_08:00:00
date -d'19700101 08:00:00' +%s
# 0
date -d'19700101 00:00:00' +%s
# -28800
md5sum /etc/localtime /usr/share/zoneinfo/Asia/Shanghai 
# 3214d0ed7947a1bcf0791dc400aa277e  /etc/localtime
# 3214d0ed7947a1bcf0791dc400aa277e  /usr/share/zoneinfo/Asia/Shanghai
# 系统时区是东八区
# 因此时间纪元要加上8小时

sysdate函数对时间纪元的计算

with
  t  as (select to_date('19700101 080000','yyyymmdd hh24miss') "Start" from dual),
  tt as (select sysdate-"Start" as "Interval" from t)
select "Interval"*24*60*60 as "Interval2sec" from tt;
-- 库的sysdate函数获取的是库所在服务器的本地时间，即是东八区
-- 时间纪元还是要加上8小时

systimestamp函数对时间纪元的计算

with
  t as   (select systimestamp head,to_char(systimestamp,'xff9') tail from dual),
  tt as  (select to_char(head,'yyyymmdd hh24miss') head,tail from t),
  ttt as (select to_date(head,'yyyymmdd hh24miss') head,tail from tt)
select (head-to_date('19700101 080000','yyyymmdd hh24miss'))*24*60*60||tail from ttt;
-- 如果使用时间戳函数，或者时间戳类型的字段计算到时间纪元的秒间隔
-- 需要将时间戳转换成date类型然后再计算
-- 两个时间戳相减之后无法计算秒间隔
-- 两个date字段相减后可以计算

[OTC]