SoFunction
Updated on 2025-04-07

PostgreSQL TIMESTAMP type Timestamp operation

PostgreSQL provides two data types that store timestamps: TIMESTAMP without time zones and TIMESTAMPTZ with time zones.

The TIMESTAMP data type can store both date and time, but it does not store time zones. This means that when the time zone where the database server is located is modified, the value stored in it will not change.

The TIMESTAMPTZ data type stores dates and times while also correctly handling time zones. PostgreSQL uses UTC values ​​to store TIMESTAMPTZ data. When inserting a value into the TIMESTAMPTZ field, PostgreSQL will automatically convert the value to a UTC value and save it to the table. When querying data from a TIMESTAMPTZ field, PostgreSQL converts the UTC value stored in it into the time zone where the database server, user, or current connection is located.

Both TIMESTAMP and TIMESTAMPTZ use 8 bytes of storage. As shown below:

SELECT
   typname,
   typlen
FROM
   pg_type
WHERE
   typname ~ '^timestamp';
  typname  | typlen
-------------+--------
 timestamp  |   8
 timestamptz |   8
(2 rows)

Important note, TIMESTAMPTZ does not store the time zone, it just has the UTC value, and then converts it to the current time zone.

Timestamp Example

First, create a table with TIMESTAMP and TIMESTAMPTZ:

CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);

Next, set the time zone of the database server to America/Los_Angeles:

SET timezone = 'America/Los_Angeles';

By the way, you can use the SHOW TIMEZONE statement to display the current time zone:

SHOW TIMEZONE;

Then, insert some sample data:

INSERT INTO timestamp_demo (ts, tstz)
VALUES
 (
 '2016-06-22 19:10:25-07',
 '2016-06-22 19:10:25-07'
 );

Finally, query:

SELECT
 ts
FROM
 timestampz_demo;
     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)

Then set the time zone to America/New_York:

SET timezone = 'America/New_York';

Query again, the results are as follows:

     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

The value of the TIMESTAMP type field remains unchanged, while the value of the TIMESTAMPTZ type field becomes the time in the current time zone.

Supplement: Notes on using postgresql timestampttz

The postgresql date and time types are divided into timestamp [ (p) ] [ without time zone ] and timestamp [ (p) ] with time zone. The difference is that one has no time zone and the other has time zone, and the storage size is 8 bytes.

At the same time, you should also pay attention to time [ (p) ] [ without time zone ] and time [ (p) ] with time zone types, the storage size of no time zone is 8 bytes, and the storage size of some time zone is 12 bytes.

Date and time type abbreviation in postgresql:

timestamp

timestamptz

date (only the year, month and day part, storage size is 4 bytes)

time

timetz

interval (represents the interval, storage size is 16 bytes)

interval can be created through the function make_interval, supporting intervals of year, month, week, day, hour, minute, second. For example, now() + make_interval(days => 10) means 10 days later, now() - make_interval(secs => 30) means 30 seconds ago

In order to ensure the accuracy of data, try to use timestamptz and timetz during the calculation process, try to avoid using timestamp and time

UTC+8(EAT - East Asian Standard Time/China Standard Time (BJT))

Note that the entire China uses the same time zone, which makes this time zone particularly large. In the westernmost part of China, the highest time of the sun is 3 pm and in the easternmost part of the sun is 11 AM. From the perspective of geographical planning alone, China spans a total of five time zones, from the East Five Districts (UTC+5) to the East Ninth District (UTC+9).

Query the definition of time zone in postgresql

select * from pg_timezone_names

Or query the East Asia/China time zone definition

select * from pg_timezone_names where utc_offset = '+08:00:00';

"PRC", "Asia/Shanghai", "Asia/Chongqing" in the query results all represent China

Generate a date time example

select make_timestamptz(1970,1,1,0,0,0.0,'Asia/Shanghai')

Be careful to avoid using timestamp-type related functions, such as: make_timestamp

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.