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

Answer 2

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

select extract(epoch from current_timestamp)::integer;

• Paul White: @JohnK.N. The explicit cast to integer is different?
• Igor: select (EXTRACT(EPOCH FROM created_at) * 1000000)::bigint to get nanoseconds
• Jasen: Technically this is give double precision float not numeric, so you may get an approximation weh converting to bigint.

Answer 3

0. shell

//test on UTC-8

# UTC-8 locale
$ date -d'2024-12-09T21:05:00' +%s
1733749500
# UTC-0
$ date -d'2024-12-09T21:05:00+00' +%s
1733778300

1. TIMESTAMP .. AT TIME ZONE ..

select extract(epoch from TIMESTAMP '2024-12-09T21:05:00' AT TIME ZONE 'UTC-8')::integer;
 date_part 
------------
 1733749500

2. TIMESTAMPTZ

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-12-09 21:05:00+08')::BIGINT AS timestamp_int;
 timestamp_int 
---------------
 1733749500

without tz in time_str, it'll use show timezone;

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-12-09 21:05:00')::BIGINT AS timestamp_int;
 timestamp_int 
---------------
 1733749500