2

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.

1
  • you can create a cast to bigint - which gets you a non-unix timestamp. or use extract (aka date_part) to get a double float unixtime.
    – Jasen
    Feb 1, 2020 at 10:15

1 Answer 1

5

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! :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.