postgres的时间间隔获取

• 时间的间隔分钟数

select extract(epoch FROM (current_timestamp- timestamp '2016-05-24 17:10:00'))/60

• 与当前时间的时间间隔

 select age(current_timestamp,timestamp '2016-05-24 16:20:00');

• 与当前时间间隔的格式化

select to_char(age(timestamp  '1994-01-10 10:00:00'), 'YYYY年MM月DD天HH24小时MI分钟');

• 时间间隔的提取

SELECT 
   CASE  WHEN EXTRACT(year FROM age(timestamp  '1994-01-10 10:00:00')) > 0 THEN ''||EXTRACT(year FROM age(timestamp  '1994-01-10 10:00:00'))||'年'
         WHEN EXTRACT(month FROM age(timestamp  '2016-01-10 10:00:00')) > 0 THEN ''||EXTRACT(month FROM age(timestamp  '1994-01-10 10:00:00'))||'月'
         WHEN EXTRACT(day FROM age(timestamp  '2016-05-10 10:00:00')) > 0 THEN ''||EXTRACT(day FROM age(timestamp  '1994-01-10 10:00:00'))||'日'
         WHEN EXTRACT(hour FROM age(timestamp  '2016-05-26 10:00:00')) > 0 THEN ''||EXTRACT(hour FROM age(timestamp  '1994-01-10 10:00:00'))||'小时'
         WHEN EXTRACT(minute FROM age(timestamp  '1994-01-10 10:00:00')) > 0 THEN ''||EXTRACT(minute FROM age(timestamp  '1994-01-10 10:00:00'))||'分钟'
         ELSE '' END

• 时间间隔计算的自定义函数

CREATE OR REPLACE FUNCTION to_time_interval_char(targetTimestamp timestamp with time zone,referenceTimestamp timestamp with time zone  DEFAULT localtimestamp)
  RETURNS text AS $$
DECLARE
  beforeOrAfterLabel TEXT ;
  intervalStr TEXT ;
  periodFormatInfo TEXT[];

BEGIN
   if targetTimestamp = referenceTimestamp THEN
   return '刚刚';
   END IF ;

   if targetTimestamp < referenceTimestamp THEN
      beforeOrAfterLabel = '前';
   ELSEIF  referenceTimestamp < targetTimestamp THEN
      beforeOrAfterLabel = '后';
   END IF ;
   periodFormatInfo =  string_to_array(to_char(age(referenceTimestamp,targetTimestamp), 'FMYYYY:FMMM:FMDD:FMHH24:FMMI'),':');

   if periodFormatInfo[1]::int != 0 THEN
    intervalStr = abs(periodFormatInfo[1]::int)::TEXT || '年' || beforeOrAfterLabel;
   ELSEIF periodFormatInfo[2]::int != 0 THEN
    intervalStr = abs(periodFormatInfo[2]::int)::TEXT || '个月' || beforeOrAfterLabel;
   ELSEIF periodFormatInfo[3]::int != 0 THEN
    intervalStr = abs(periodFormatInfo[3]::int)::TEXT || '天' || beforeOrAfterLabel;
   ELSEIF periodFormatInfo[4]::int != 0 THEN
    intervalStr = abs(periodFormatInfo[4]::int)::TEXT || '小时' || beforeOrAfterLabel;
   ELSEIF periodFormatInfo[5]::int != 0 THEN
    intervalStr = abs(periodFormatInfo[5]::int)::TEXT || '分钟' || beforeOrAfterLabel;
   ELSE
    intervalStr = '1分钟' || beforeOrAfterLabel;
   END IF;
   RETURN intervalStr ;
  RETURN '';
END ;
$$
LANGUAGE plpgsql;

使用示例