Postgresql – Postgres epoch from current_timestamp

postgresqltimestamptimezone

I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries:

select  extract( epoch from current_timestamp::timestamp with time zone at time zone 'Asia/Tehran');
1588605420.89165


select  extract( epoch from current_timestamp::timestamp with timezone at time zone 'UTC');
1588589296.18219

which there is a deficit of 16200 (4 and a half hours). Why is that when it explicitly declares that current_timestamp is in a specific time zone. Should both epochs (UNIX timestamp) not map to the same amount of seconds?

what I don't get maybe better displayed on contrast with this small python code:

>>> import time
>>> import pytz
>>> import datetime
>>> teh_tz = pytz.timezone('Asia/Tehran')
>>> utc_tz = pytz.timezone('UTC')

>>> datetime.datetime.now(teh_tz)
datetime.datetime(2020, 5, 4, 15, 31, 11, 14869, tzinfo=<DstTzInfo 'Asia/Tehran' +0430+4:30:00 DST>)
>>> datetime.datetime.now(utc_tz)
datetime.datetime(2020, 5, 4, 11, 1, 16, 198437, tzinfo=<UTC>)

>>> datetime.datetime.now(teh_tz).timestamp()
1588590087.590199
>>> datetime.datetime.now(utc_tz).timestamp()
1588590091.901826

As it's seen, different time zones have differences in their current time but all map to the same amount of seconds (UNIX timestamp).
I guess I'm totally lost in here, please save me.

Best Answer

The documentation of extract clearly states:

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

If you want to know how many seconds passed since Jan. 1st 1970 in your time zone, use

extract(epoch FROM localtimestamp)

The result of AT TIME ZONE, when applied to a timestamp with time zone, is always a timestamp without time zone. extract will interpret such a time stamp in your current time zone (it does not know about the second argument you passed to AT TIME ZONE).

Your comment suggests that you want to get the (absolute) epoch at the beginning of the current day in your time zone. That would be

extract(epoch FROM date_trunc('day', current_timestamp))
Related Question