PostgreSQL如何创建存储PST时间戳而非UTC的表？

First, Let's Clear Up the Syntax Confusion #

The square brackets [] in the type definition mark optional elements, while the parentheses (p) stand for an optional precision parameter:

• timestamp with time zone (or its shorthand timestamptz) is the base type, storing timestamps with microsecond precision by default.
• The (p) lets you set the number of fractional digits for seconds, ranging from 0 to 6. For example:
  • timestamp(3) with time zone stores timestamps rounded to milliseconds (3 decimal places for seconds)
  • timestamp(0) with time zone stores timestamps without any fractional seconds

How to Work with PST Times for Your some_time Column #

First, a critical note: PostgreSQL's timestamptz type always stores timestamps internally in UTC—it doesn't save the original timezone directly. What you can control is how input gets converted to UTC, and how stored UTC times are displayed back to you. Here's how to align this with PST:

1. Switch Your Column to timestamptz
   Your original table uses TIMESTAMP (the timezone-agnostic type), which stores literal time values without any timezone context. To handle timezone conversions, change the column type:

CREATE TABLE "mytable" (
  id SERIAL PRIMARY KEY,
  some_time TIMESTAMPTZ -- Shorthand for TIMESTAMP WITH TIME ZONE
);

2. Set Default Timezone to PST (America/Los_Angeles)
   Instead of relying on UTC for input/output, configure your session or database to use PST:

• For the current session only:

  SET TIME ZONE 'America/Los_Angeles';
  

  Now any timestamps you insert will be converted from PST to UTC for storage, and queries will return UTC timestamps converted back to PST.
• For the entire database (persistent setting):

  ALTER DATABASE your_db_name SET TIME ZONE 'America/Los_Angeles';
  

  You'll need to reconnect to the database for this change to take effect.

3. Explicitly Specify PST When Inserting Data
   If you don't want to change global/session timezone settings, you can tag your input values with the PST timezone (or its official identifier) when inserting:

-- Using the timezone identifier (recommended to avoid daylight saving time ambiguity)
INSERT INTO mytable (some_time) VALUES ('2024-05-20 10:00:00 America/Los_Angeles');

-- Using the PST abbreviation (works but less reliable for DST)
INSERT INTO mytable (some_time) VALUES ('2024-05-20 10:00:00 PST');

When you query this data, if your session timezone is set to PST, it will display as 2024-05-20 10:00:00-07 (or -08 during standard time), but internally it's stored as UTC.

Pro Tip #

Always use full timezone identifiers like America/Los_Angeles instead of abbreviations like PST. Abbreviations can be ambiguous (multiple regions might use the same abbreviation) and don't automatically account for daylight saving time transitions.

内容的提问来源于stack exchange，提问作者AK91