How to get the timestamp column in only milliseconds from PostgreSQL?
Use EXTRACT
and the UNIX-Timestamp
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;
would give
1305621628876.94
Multiply it by 1000
to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created
being the column with your timetamp) that illustrates how to work with timezones:
SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;
--EDIT--
I've discovered this (see below) is basically wrong. See How do I get the current unix timestamp from PostgreSQL? for the source of my confusion...
--END EDIT--
Posting as an answer because it won't work as a comment.
testbed:
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
create table my_table(created timestamp);
insert into my_table(created) values(now()),('1970-01-01');
\d my_table
Table "stack.my_table"
Column | Type | Modifiers
---------+-----------------------------+-----------
created | timestamp without time zone |
queries:
select created, extract(epoch from created) from my_table;
created | date_part
---------------------------+------------------
2011-05-17 13:18:48.03266 | 1305634728.03266
1970-01-01 00:00:00 | -3600
select created, extract(epoch from date_trunc('milliseconds', created))
from my_table;
created | date_part
---------------------------+------------------
2011-05-17 13:18:48.03266 | 1305634728.03266
1970-01-01 00:00:00 | -3600
select created, extract(epoch from created at time zone 'UTC') from my_table;
created | date_part
---------------------------+------------------
2011-05-17 13:18:48.03266 | 1305638328.03266
1970-01-01 00:00:00 | 0
note date_part
in the third query is: 1305638328.03266 - 3600 different.