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.

score 30 · Accepted Answer · answered Sep 22 '14 at 16:18

30

extract(epoch

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

answered Sep 22 '14 at 16:18

Clodoaldo Neto

1 Answers1