Introduction to PostgreSQL timestamp

PostgreSQL provides you with two temporaldata types for handling timestamps:

• timestamp: a timestamp without a timezone one.
• timestamptz: timestamp with a timezone.

The timestamp datatype allows you to store bothdate and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.

The timestamptz datatype is the timestamp with a timezone. Thetimestamptz data type is a time zone-aware date and time data type.

Internally, PostgreSQL stores thetimestamptz in UTC value.

• When you insert a value into atimestamptz column, PostgreSQL converts thetimestamptz value into a UTC value and stores the UTC value in the table.
• When you retrieve data from atimestamptz column, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection.

Notice that bothtimestamp andtimestamptz uses 8 bytes for storing the timestamp values as shown in the following query:

SELECT  typname,  typlenFROM  pg_typeWHERE  typname ~ '^timestamp';

Output:

typname   | typlen-------------+-------- timestamp   |      8 timestamptz |      8(2 rows)

It’s important to note that PostgreSQL storestimestamptz values in the database using UTC values. It does not store any timezone data with thetimestamptz value.

PostgreSQL timestamp example

Let’s take a look at an example of using thetimestamp and timestamptzto have a better understanding of how PostgreSQL handles them.

First,create a table that consists of bothtimestamp thetimestamptz columns.

CREATE TABLE timestamp_demo (    tsTIMESTAMP,    tstzTIMESTAMPTZ);

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

SET timezone= 'America/Los_Angeles';

By the way, you can see the current time zone using theSHOW TIMEZONE command:

SHOW TIMEZONE;

TimeZone--------------------- America/Los_Angeles(1 row)

Then,insert a new row into thetimstamp_demotable:

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

After that,query data from thetimestamp and timestamptz columns.

SELECT   ts, tstzFROM   timestamp_demo;

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

The query returns the same timestamp values as the inserted values.

Finally, change the timezone of the current session toAmerica/New_York and query data again.

SET timezone= 'America/New_York';

SELECT  ts,  tstzFROM  timestamp_demo;

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

The value in thetimestamp column does not change whereas the value in thetimestamptz column is adjusted to the new time zone of'America/New_York'.

Generally, it is a good practice to use thetimestamptz data type to store the timestamp data.

PostgreSQL timestamp functions

To handle timestamp data effectively, PostgreSQL provides some handy functions as follows:

Getting the current time

To get the current timestamp you use theNOW() function as follows:

SELECT NOW();

Output:

now------------------------------- 2024-01-31 21:01:58.985943-05(1 row)

Alternatively, you can use theCURRENT_TIMESTAMP function:

SELECT CURRENT_TIMESTAMP;

Output:

current_timestamp------------------------------- 2024-01-31 21:02:04.715486-05(1 row)

To get the current time without a date, you use theCURRENT_TIME function:

SELECT CURRENT_TIME;

Output:

current_time-------------------- 21:02:13.648512-05(1 row)

Note that bothCURRENT_TIMESTAMP andCURRENT_TIME return the current time with the time zone.

To get the time of day in the string format, you use thetimeofday() function.

SELECT TIMEOFDAY();

timeofday------------------------------------- Wed Jan 31 21:02:20.840159 2024 EST(1 row)

Convert between timezones

To convert a timestamp to another time zone, you use thetimezone(zone, timestamp) function.

SHOW TIMEZONE;

TimeZone------------------ America/New_York(1 row)

The current timezone isAmerica/New_York.

To convert2016-06-01 00:00 toAmerica/Los_Angeles timezone, you use thetimezone() function as follows:

SELECT timezone('America/Los_Angeles','2016-06-01 00:00');

timezone--------------------- 2016-05-31 21:00:00(1 row)

Note that we pass the timestamp as a string to thetimezone() function, PostgreSQL casts it totimestamptz implicitly. It is better to cast a timestamp value to thetimestamptz data type explicitly as the following statement:

SELECT timezone('America/Los_Angeles','2016-06-01 00:00'::timestamptz);

Output:

timezone--------------------- 2016-05-31 21:00:00(1 row)

Using default values for timestamp columns

First, create a new table calleddepartment:

CREATE TABLEdepartment(    id SERIAL PRIMARY KEY,    name VARCHAR NOTNULL,    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP);

The default values for thecreated_at andupdated_at columns are the current timestamp provided by theCURRENT_TIMESTAMP function.

Second, insert a new row into thedepartment table without specifying the values for thecreated_at andupdated_at columns:

INSERT INTO department(name)VALUES('IT')RETURNING *;

Output:

id | name |          created_at           |          updated_at----+------+-------------------------------+-------------------------------  1 | IT   | 2024-01-31 21:25:31.162808-05 | 2024-01-31 21:25:31.162808-05(1 row)

The output indicates that PostgreSQL uses the current time to insert into thecreated_at andupdated_at columns.

When you update a row in thedepartment table, theupdated_at column will not be updated to the current time automatically.

To update the value in the updated_at column to the time the row is updated, you can create aBEFORE UPDATE trigger to change the value in theupdated_at column.

Note that MySQL offers theON UPDATE CURRENT_TIMESTAMP to automatically update aTIMESTAMP column to the current timestamp. PostgreSQL does not support this feature at the moment.

Third, create aBEFORE UPDATE trigger to update theupdated_at column of thedepartment table:

CREATE OR REPLACE FUNCTION update_updated_at()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = current_timestamp;    RETURN NEW;END;$$LANGUAGE plpgsql;CREATE TRIGGER department_updated_at_triggerBEFORE UPDATE ON departmentFOR EACH ROWEXECUTE FUNCTION update_updated_at();

Fourth, update the name of the IT department to ITD without specifying a value for theupdated_at column:

UPDATE departmentSET name = 'ITD'WHERE id = 1RETURNING *;

Output:

id | name |          created_at           |          updated_at----+------+-------------------------------+-------------------------------  1 | ITD  | 2024-01-31 21:25:31.162808-05 | 2024-01-31 21:25:51.318803-05(1 row)

The output indicates that the value in theupdated_at column has been updated automatically by the trigger.

Summary

• Usetimestamp andtimestamptz to store timestamp data.
• PostgreSQL stores thetimestamptz values in the database as UTC values.

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL timestamp
• PostgreSQL timestamp example
• PostgreSQL timestamp functions
• Using default values for timestamp columns
• Summary