PostgreSQL timestamp vs timestamptz

Introduction

In almost every application of a database you will at some point need to store dates and times. You may need to store the the birthdate of a user or the date and time an article was published. PostgreSQL offers timestamp and timestamptz along with tons of utility functions that will allow you to perform most date and time related tasks. In this article we will be discussing the difference between these two data types. We hope to clear up when to use PostgreSQL timestamp vs timestamptz so that you can decide on the appropriate data type for your specific application.

We will also review some utility functions that will allow you to perform some handy date/time related tasks.

Timestamp vs Timestamptz – What’s the Difference?

The big difference between these two data types is that timestamptz includes a timezone offset while timestamp does not. So it is important to decide for the timestamp you are storing whether the timezone is important to keep or not.

How Timestamptz Works Under the Hood

timestamptz stores the date and time info in the form of a UTC value.

What is UTC?

UTC is an abbreviation for Coordinated Universal Time. It is the standard by which the world keeps times coordinated. It’s a standardized time that we agree upon and you can calculate your local time based on an offset from this universal time based on our time zone. Each time zone will have a different offset. UTC time does not move any because of daylight savings time, but if your local time observes daylight savings time it’s because your offset changes based on the time of year.

Now that we understand what UTC is, let’s reiterate. timestamptz stores data in a UTC value. This value has no timezone data attached. So how does PostgreSQL calculate the time with time zone accounted for? It uses the timezone set by the server.

Storage

They both use 8 bytes for storage so there is no advantage in terms of storage of using timestamp vs timestamptz.

Usage

Set the Timezone

To set the timezone for use with timestamptz you can utilize a query like this:

Show the Timezone

You can view which timezone is currently being used with this command:

Example Usage

Let’s see an example of creating a table with both types so you can see how minor the difference is since most of the work is done internally for timstamptz.

Now to insert data into the table:

Notice how the insert was the same because the timezone is taken care of only when you retrieve the data.

Now let’s select the data and see how it differs:

As you can see now the two times are different because ttz has taken our timezone into account.

Conclusion

We hope you understand the difference between PostgreSQL timestamp vs timestamptz more clearly now. Thank you for joining us for this demo!