CODEWITHSUNDEEP
postgresql
sungiant
sungiant

Reputation: 3222

Postgres timestamp to unix time in milliseconds as a bigint

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.

Upvotes: 15

Views: 39392

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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

Upvotes: 34

Related Questions