PostgreSQL Timestamp Data Types

PostgreSQL Timestamp Data Types
Summary: in this tutorial, you will learn about the PostgreSQL timestamp data types
including timestamp and timestamptz. You will also learn how to use some handy
functions to handle timestamp data effectively.
Create a free Postgres Database in 0.3 seconds on Neon. Ship faster with database
branching. Handle peaks efficiently with autoscaling.
Sponsored
Introduction to PostgreSQL timestamp
PostgreSQL provides you with two temporal data types for handling timestamps:
 timestamp: a timestamp without a timezone one.

 timestamptz: timestamp with a timezone.
The timestamp datatype allows you to store both date 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. The timestamptz data
type is a time zone-aware date and time data type.
Internally, PostgreSQL stores the timestamptz in UTC value.
 When you insert a value into a timestamptz column, PostgreSQL converts the
timestamptz value into a UTC value and stores the UTC value in the table.
 When you retrieve data from a timestamptz 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 both timestamp and timestamptz uses 8 bytes for storing the timestamp
values as shown in the following query:
SELECT
typname,
typlen
FROM
pg_type
WHERE
typname ~ '^timestamp';Code language: SQL (Structured Query Language) (sql)
Output:
typname | typlen
-------------+--------
timestamp | 8
timestamptz | 8
(2 rows)Code language: SQL (Structured Query Language) (sql)
It’s important to note that PostgreSQL stores timestamptz values in the database using
UTC values. It does not store any timezone data with the timestamptz value.
PostgreSQL timestamp example

Let’s take a look at an example of using the timestamp and timestamptz to have a
better understanding of how PostgreSQL handles them.
First, create a table that consists of both timestamp the timestamptz columns.
CREATE TABLE timestamp_demo (

ts TIMESTAMP,
tstz TIMESTAMPTZ
);Code language: SQL (Structured Query Language) (sql)
Next, set the time zone of the database server to America/Los_Angeles.
SET timezone = 'America/Los_Angeles';Code language: SQL (Structured Query

Language) (sql)
By the way, you can see the current time zone using the SHOW TIMEZONE command:
SHOW TIMEZONE;Code language: SQL (Structured Query Language) (sql)

TimeZone
---------------------
America/Los_Angeles
(1 row)Code language: SQL (Structured Query Language) (sql)
Then, insert a new row into the timstamp_demo table:
INSERT INTO timestamp_demo (ts, tstz)

VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');Code language:
SQL (Structured Query Language) (sql)
After that, query data from the timestamp and timestamptz columns.
SELECT
ts, tstz
FROM
timestamp_demo;Code language: SQL (Structured Query Language) (sql)
ts | tstz
---------------------+------------------------
2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
The query returns the same timestamp values as the inserted values.
Finally, change the timezone of the current session to America/New_York and query
data again.
SET timezone = 'America/New_York';Code language: SQL (Structured Query Language)

(sql)
SELECT
ts,
tstz
FROM
timestamp_demo;Code language: SQL (Structured Query Language) (sql)
ts | tstz
---------------------+------------------------
2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
The value in the timestamp column does not change whereas the value in the
timestamptz column is adjusted to the new time zone of 'America/New_York'.
Generally, it is a good practice to use the timestamptz 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 the NOW() function as follows:
SELECT NOW();Code language: SQL (Structured Query Language) (sql)
Output:
now
-------------------------------
2024-01-31 21:01:58.985943-05
(1 row)
Code language: SQL (Structured Query Language) (sql)
Alternatively, you can use the CURRENT_TIMESTAMP function:
SELECT CURRENT_TIMESTAMP;Code language: SQL (Structured Query Language) (sql)
Output:
current_timestamp
-------------------------------
2024-01-31 21:02:04.715486-05
(1 row)
Code language: CSS (css)
To get the current time without a date, you use the CURRENT_TIME function:
SELECT CURRENT_TIME;
Output:
current_time
--------------------
21:02:13.648512-05
(1 row)
Code language: SQL (Structured Query Language) (sql)
Note that both CURRENT_TIMESTAMP and CURRENT_TIME return the current time with the
time zone.
To get the time of day in the string format, you use the timeofday() function.
SELECT TIMEOFDAY();Code language: SQL (Structured Query Language) (sql)

timeofday
-------------------------------------
Wed Jan 31 21:02:20.840159 2024 EST
Convert between timezones
To convert a timestamp to another time zone, you use the timezone(zone,

timestamp) function.
SHOW TIMEZONE;Code language: SQL (Structured Query Language) (sql)

TimeZone
------------------
America/New_York
The current timezone is America/New_York.
To convert 2016-06-01 00:00 to America/Los_Angeles timezone, you use the

timezone() function as follows:
SELECT timezone('America/Los_Angeles','2016-06-01 00:00');Code language:

SQL (Structured Query Language) (sql)
timezone
---------------------
2016-05-31 21:00:00
Note that we pass the timestamp as a string to the timezone() function, PostgreSQL
casts it to timestamptz implicitly. It is better to cast a timestamp value to the
timestamptz data type explicitly as the following statement:
SELECT timezone('America/Los_Angeles','2016-06-01
00:00'::timestamptz);Code language: SQL (Structured Query Language) (sql)
Output:
timezone
---------------------
2016-05-31 21:00:00
(1 row)Code language: CSS (css)
Using default values for timestamp columns

First, create a new table called department:
CREATE TABLE department (

id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);Code language: PHP (php)
The default values for the created_at and updated_at columns are the current
timestamp provided by the CURRENT_TIMESTAMP function.
Second, insert a new row into the department table without specifying the values for
the created_at and updated_at columns:
INSERT INTO department(name)

VALUES('IT')
RETURNING *;Code language: JavaScript (javascript)
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 the
created_at and updated_at columns.
When you update a row in the department table, the updated_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 a BEFORE UPDATE trigger to change the value in the updated_at column.
Note that MySQL offers the ON UPDATE CURRENT_TIMESTAMP to automatically update a

TIMESTAMP column to the current timestamp. PostgreSQL does not support this feature
at the moment.
Third, create a BEFORE UPDATE trigger to update the updated_at column of the
department 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_trigger

BEFORE UPDATE ON department
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
Fourth, update the name of the IT department to ITD without specifying a value for the
updated_at column:
UPDATE department
SET name = 'ITD'
WHERE id = 1
RETURNING *;Code language: JavaScript (javascript)
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 the updated_at column has been updated
automatically by the trigger.
Summary
 Use timestamp and timestamptz to store timestamp data.
 PostgreSQL stores the timestamptz values in the database as UTC values.

PostgreSQL Timestamp Data Types

Uploaded by

Copyright:

Available Formats

PostgreSQL Timestamp Data Types

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PostgreSQL Timestamp Data Types

Uploaded by

Copyright:

Available Formats

PostgreSQL Timestamp Data Types

Introduction to PostgreSQL timestamp

 timestamp: a timestamp without a timezone one.

Internally, PostgreSQL stores the timestamptz in UTC value.

PostgreSQL timestamp example

CREATE TABLE timestamp_demo (

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

SET timezone = 'America/Los_Angeles';Code language: SQL (Structured Query

SHOW TIMEZONE;Code language: SQL (Structured Query Language) (sql)

Then, insert a new row into the timstamp_demo table:

INSERT INTO timestamp_demo (ts, tstz)

SET timezone = 'America/New_York';Code language: SQL (Structured Query Language)

PostgreSQL timestamp functions

Getting the current time

SELECT NOW();Code language: SQL (Structured Query Language) (sql)

Alternatively, you can use the CURRENT_TIMESTAMP function:

SELECT CURRENT_TIMESTAMP;Code language: SQL (Structured Query Language) (sql)

SELECT TIMEOFDAY();Code language: SQL (Structured Query Language) (sql)

Convert between timezones

To convert a timestamp to another time zone, you use the timezone(zone,

SHOW TIMEZONE;Code language: SQL (Structured Query Language) (sql)

The current timezone is America/New_York.

To convert 2016-06-01 00:00 to America/Los_Angeles timezone, you use the

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

Using default values for timestamp columns

CREATE TABLE department (

INSERT INTO department(name)

id | name | created_at | updated_at

Note that MySQL offers the ON UPDATE CURRENT_TIMESTAMP to automatically update a

CREATE OR REPLACE FUNCTION update_updated_at()

CREATE TRIGGER department_updated_at_trigger

id | name | created_at | updated_at

You might also like