How to get the timezone from a PostgreSQL timestamp

This is probably a very simple question, but it's hard to Google because it matches too many different questions.

I want to get the timezone (or offset) of timestamps saved in my database.

For example, right now in my database I have

expired_at      
---------------------
 2018-04-28 00:00:00
 2018-03-28 08:00:00
 2018-02-28 05:00:00

I want to find all expired_at that has the PST offset.

So my pseudo-code would be something like this

SELECT expired_at FROM table WHERE expired_at IS IN TIMEZONE('PST')

Can I get some help. Thanks!

PS. Basically I am trying to fix a data integrity issue by finding all UTC timestamp and updating them to be PST. The database is screwed up right now with a mix of many different timezones.

The documentation on Postgres timestamps says:


For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
Time, GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If
no time zone is stated in the input string, then it is assumed to be
in the time zone indicated by the system's TimeZone parameter, and is
converted to UTC using the offset for the timezone zone.

Contrary to what a reasonable person might expect when reading timestamp with time zone, this Postgres datatype doesn't actually store a timezone. Internally, the value is always converted to UTC.

The information about the input timezone is lost when saving the value to the database. As far as I know, there is no way to get the information you seek.

StackExchange Database Administrators Q#164203, answer score: 29