How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

Question

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format?

I found this question, but it shows only how to convert timestamp without time zone to unix timestamp.

Answer 1

To solve this, I looked here.

For example, I'm in a time zone which is at UTC + 1,

so,

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';

gives:

2019-06-06 17:41:00.216647

which is 1 hour behind clock time because I'm on UTC + 1.

So, then from here, I got SELECT EXTRACT(EPOCH FROM ts) FROM data

Combining the two gives:

SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'));

which for me gives:

date_part
1559843103.53928

YMMV - fiddle available here - put your timestampz into the formula for current_timestamp and you should be golden!

Interstingly, you can check your results here.

p.s. welcome to the forum! :-)