Postgres epoch from current_timestamp

Question

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.

Answer 1

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. 1^st 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))

• Ali Ebrahimi: Does it mean that in above queries for calculating seconds, the beginning of time points is same but the endings are different (which are provided in queries)?, I dont seem to get the point.
• Laurenz Albe: Yes, exactly. The beginning the same (midnight Jan 1 1970 in your time zone). The first end point is what the wall clock currently shows in Teheran, but interpreted in your current time zone. The second end point is what an UTC wall clock currently shows, again interpreted in your current time zone. What is the value you would like to get?
• Ali Ebrahimi: Since time stored as epoch in DB and I need to query them based on current_timestamp on my timezone (4 and half hours after UTC), I wanna map it (current_timestamp on my timezone) to the same epoch, UTC current_timestamp mapped to. I'm stuck.
• Laurenz Albe: Erm, I cannot understand that. What is the result you need?
• user1822: @aliebrahimi "since their time stored as epoch" - which is the root cause of your problem. You should have used timestamptz for that. Storing an epoch is almost always the wrong choice in a database (and your current problem is a direct result of that decision). See e.g. here blog.sql-workbench.eu/post/epoch-mania