How to get the timezone from a PostgreSQL timestamp

Question

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.

• Clockwork-Muse: Side note: all events (including expiration stamps, like what you have here), should be stored "ignorant" about timezones/offsets. This doesn't really mean "has no timezone" (ie, TIMESTAMP WITHOUT TIMEZONE), but rather should be a "doesn't care" situation; this is usually done by explicitly storing everything in UTC. The reasoning has to do with the fact that an event represents an actual, distinct, specific instant in time, and isn't really bound to any calendar.
• Clockwork-Muse: Also, even if there was a function that would allow you to go from hours to time zone, it would generate more problems than it would solve: There are multiple zones with the same offset(s), most of them with different rules. There's no way to pick. At GMT-4:00, there's at least four different rule sets, possibly more. If you're in DST, Pacific Time is only -7:00, too.
• OrangeDog: Also note that PST is not a time zone, it's a time offset. For the zone you'd want to use something like US/Pacific or America/Los_Angeles.

Answer 1

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.

• vinhboy: Yea you're right. I am an idiot. That's the hours, not the timezone. Thanks!

Answer 2

The data you want does not exist. Timestamps do not contain time zone data. Timestamps are always stored in UTC. The time zone information is used to convert to UTC on the way into the database, and to format UTC into some other format on the way out. It is not stored, unless you create an additional column to store it in.

Answer 3

It, probably, depends on the version of your SQL and how table was formatted. Accordingly to https://www.postgresql.org/docs/current/functions-datetime.html you can extract the timezone using the keyword

SELECT EXTRACT(timezone_hour FROM your_timestamp_column)

that gives "The hour component of the time zone offset" (also you may consider "timezone" and "timezone_minute"). There is also option to extract timezone_minute for the half-hour zones

• user1822: there is no function timezone_hour() in Postgres. And as Postgres doesn't actually store the timezone, there is no way to retrieve that information
• user1822: @DanyloZherebetskyy: dbfiddle.uk/…
• OrangeDog: @DanyloZherebetskyy sucks for the ~1.5 billion people living in a half-hour time zone then.
• user1822: As Postgres does not store a time zone value in a timestamp with time zone that only retrieves the time zone hours from the current session time zone: dbfiddle.uk/… It's essentially the same as show timezone;
• user1822: Quote from the manual "For timestamp with time zone, the internally stored value is always in UTC [...]. An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone"