PostgreSQL 计算指定的日期时间相对于指定的日期经过了多少秒

/****************************************************************************************
    计算指定的日期时间相对于指定的日期经过了多少秒
drop function if exists date_diff_second(timestamptz);
****************************************************************************************/
create or replace function  date_diff_second(timestamptz)
  returns float8
as $$
	with cte as(
		select ($1-($1::date)) as diff
	)select 
		(
			(extract(hour from diff) * 3600) +
			(extract(minute from diff) * 60) +
			extract(second from diff)
		)
	from cte;
$$ language sql strict;
--计算相对于2018-04-12日经过了多少秒
select date_diff_second('2018-04-12 10:11:09.043825+08'::timestamptz)
--验证计算结果
select '2018-04-12 10:11:09.043825+08', ('2018-04-12'::date + make_interval(secs=>36669.043825))::timestamptz

判断闰年还是平年,闰年一年有366天,平年一年有365天
遵循的规律为: 四年一闰,百年不闰,四百年再闰.
摘自postgresql10.3源码src\include\utils\datetime.h(273)

#define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))

/****************************************************************************************
    判断闰年还是平年,闰年一年有366天,平年一年有365天
drop function if exists isleap(timestamp);
drop function if exists isleap(timestamptz);
drop function if exists isleap(date);
****************************************************************************************/
create or replace function  isleap(timestamptz)
  returns boolean
as $$
	select (((y) % 4) = 0 and (((y) % 100) <> 0 or ((y) % 400) = 0))
		from cast(extract(year from $1) as integer ) as y
$$ language sql strict;

create or replace function  isleap(timestamp)
  returns boolean
as $$
	select (((y) % 4) = 0 and (((y) % 100) <> 0 or ((y) % 400) = 0))
		from cast(extract(year from $1) as integer ) as y
$$ language sql strict;

create or replace function  isleap(date)
  returns boolean
as $$
	select (((y) % 4) = 0 and (((y) % 100) <> 0 or ((y) % 400) = 0))
		from cast(extract(year from $1) as integer ) as y
$$ language sql strict;

验证结果:

with cte as(
    select (case when isleap(year) then 
                366
            else
                365
            end) as days
    from generate_series(1900,2018-1) as year
),calc as(
    select sum(days) as f1,('2018-01-01'::date - '1900-01-01'::date )  as f2 from cte
)select *, f1*24*3600 from calc

  f1   |  f2   |  ?column?  
-------+-------+------------
 43099 | 43099 | 3723753600

转载自：https://blog.csdn.net/kmblack1/article/details/79910895