postgrepsql 时间转换

一、unix时间转字符串时间

1.SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1491373612 * INTERVAL '1 second';

2.select  to_char(to_timestamp(time),'yyyy-MM-dd HH24-MI-SS') as time  from tablename;

3.select  to_timestamp(1284352323)

二、字符串时间转unix时间

1. select extract(epoch from timestamp WITH TIME ZONE '2017-04-05 14:26:52');

三、字符串时间转unix时间

1.SELECT split_part('172.16.1.28','.',1)::bigint*16777216 + split_part('172.16.1.28','.',2)::bigint*65536 +
 split_part('172.16.1.28','.',3)::bigint*256 + split_part('172.16.1.28','.',4)::bigint;