PostgreSQL time and Unix timestamp
1.unix timestamp-"time"
Second
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1471313356 * INTERVAL' 1 second'
? column?
-
2016-08-16 10 09R 16 16 8
(1 row)
# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 1471313356 * INTERVAL' 1 second'
? column?
-
2016-08-16 02:09:16
(1 row)
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1461033436.269 * INTERVAL' 1 second'
? column?
-
2016-04-19 10 37RU 16.269V 08
(1 row)
Millisecond
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1464186650352 * INTERVAL' 1 milliseconds'
? column?
-
2016-05-25 22-30-50-35-52-08
(1 row)
# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 1464186650352 * INTERVAL' 1 milliseconds'
? column?
-
2016-05-25 14 140 30 50.352
(1 row)
#
two。 Time-"Unix timestamp"
Second
# SELECT EXTRACT (EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2016-05-25 1415 3015) 50.352')
Date_part
-
1464186650.352
(1 row)
# SELECT EXTRACT (EPOCH FROM TIMESTAMP WITH TIME ZONE '2016-05-25 1415 3015) 50.352')
Date_part
-
1464157850.352
(1 row)
Millisecond
# SELECT EXTRACT (EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2016-05-25 1414 30mer 50.352') * 1000
? column?
-
1464186650352
(1 row)
#
Postgres=# SELECT extract (epoch FROM '2017-02-07 11:18:23.098+08'::timestamp with time zone)
Date_part
-
1486437503.098
(1 row)
Postgres=# select to_timestamp (1486437503.098)
To_timestamp
-
2017-02-07 11 purl 18 purl 23.098 Vol 08
(1 row)
Postgres=#
