Postgres timestamp to unix time in milliseconds as a bigint

Question

How can I get the following snippet to work in postgres:

ALTER TABLE mytable
ADD COLUMN create_time_utc bigint not null
DEFAULT (now() at time zone 'utc');

I want the new column create_time_utc to be the unix time in milliseconds (i.e number of milliseconds since Unix epoch January 1 1970).

I know I need to convert the postgres timestamp to a bigint, but I'm not sure how to do that.

Answer 1

extract(epoch

alter table mytable
add column create_time_utc bigint not null
default (extract(epoch from now()) * 1000);

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT