转时间戳函数
create or eplace function data_to_timestamp(datestr timestamp)
RETURNS BIGINT AS
B
O
D
Y
BODY
BODY
DELCARE
result BIGINT;
BEGIN
result :=extract(epoch FROM date_trunc(‘MS’,to_timestamp(cast(datestr as varchar),‘YYYY-MM-DD HH24:MI:SS.MS’)))*1000;
RETURN result;
END;
B
O
D
Y
BODY
BODY
LANGUAGE ‘plgsql’ immutable;
create or eplace function data_to_timestamp(datestr bigint)
RETURNS BIGINT AS
B
O
D
Y
BODY
BODY
DELCARE
result BIGINT;
BEGIN
RETURN datestr ;
END;
B
O
D
Y
BODY
BODY
LANGUAGE ‘plgsql’ immutable;
–immutable是为了实现函数加索引
–通过data_to_timestamp11函数可以获取某一个时间戳对应零点时间戳的值。
create or eplace function data_to_timestamp11(datestr timestamp without time zone)
RETURNS BIGINT AS
B
O
D
Y
BODY
BODY
DELCARE
result BIGINT;
BEGIN
result :=extract(epoch FROM date_trunc(‘MS’,to_timestamp(cast(datestr as varchar),‘YYYY-MM-DD 00:00:00.0’)))*1000;
RETURN result;
END;
B
O
D
Y
BODY
BODY
LANGUAGE ‘plgsql’ immutable;