Search code examples
postgresqlepochtimestamp-with-timezone

save epoch time of on column into another column postgres

I have a table with two field in postgresql. One field is creation_time which is a timestamp with timezone and is filled with datetimes. Other field is foo which is filled by null values and I want to update this column by the epoch time of other column for each row.

How this is possible? I know that extract(epoch) can convert timestamp to epoch, but I don't know how to use it for my purpose.

Solution

  • This should work. foo should be of type bigint. 

    UPDATE table SET foo = EXTRACT(epoch FROM creation_time)