CREATE OR REPLACE FUNCTION datestr_to_timestamp(datestr VARCHAR)
RETURNS BIGINT AS
$BODY$
--由日期字符串格式为2011-12-05 19:10:00转换成long:1323083400 * 1000
--由于可能会出现传入参数带秒,故这里都将秒舍去
DECLARE
result BIGINT;
BEGIN
result := extract(epoch FROM date_trunc('minute', to_timestamp(datestr, 'YYYY-MM-DD HH24:MI:SS')));
RETURN result * 1000;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION timestamp_to_datestr(date BIGINT)
RETURNS VARCHAR AS
$BODY$
--由时间戳转换成日期字符串 参数形如:1323083400000 转换后为2011-12-05 19:10:00
DECLARE
result VARCHAR;
BEGIN
result := to_char(to_timestamp(date/1000), 'YYYY-MM-DD HH24:MI:SS');
RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
调用示例:
select datestr_to_timestamp('2011-12-05 19:10:00');
select timestamp_to_datestr(1323083400000);
postgresql 存储过程函数:时间戳与日期字符串相互转换
最新推荐文章于 2024-07-22 06:51:13 发布