3

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.

0

2 Answers 2

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

1

The following returns numeric without decimal digits, if that is preferred:

select extract(epoch from current_timestamp)::integer;
2
  • 1
    @JohnK.N. The explicit cast to integer is different?
    – Paul White
    Dec 28, 2022 at 15:58
  • select (EXTRACT(EPOCH FROM created_at) * 1000000)::bigint to get nanoseconds
    – Igor
    Apr 4, 2023 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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