15

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.

Improve this question
3
  • 2
    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
    Feb 13, 2017 at 23:18
  • 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.
    – Clockwork-Muse
    Feb 13, 2017 at 23:27
  • 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.
    – OrangeDog
    Oct 10, 2020 at 22:07

3 Answers 3

Reset to default
28

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.

Improve this answer
1
  • Yea you're right. I am an idiot. That's the hours, not the timezone. Thanks!
    – vinhboy
    Feb 13, 2017 at 19:57
4

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.

Improve this answer
-1

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

Improve this answer
14
  • 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
    Jan 10, 2020 at 6:57
  • 1
    @DanyloZherebetskyy: dbfiddle.uk/…
    – user1822
    Oct 9, 2020 at 20:20
  • 1
    @DanyloZherebetskyy sucks for the ~1.5 billion people living in a half-hour time zone then.
    – OrangeDog
    Oct 10, 2020 at 12:08
  • 1
    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
    Oct 16, 2020 at 5:41
  • 1
    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"
    – user1822
    Oct 19, 2020 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.