PostgreSQL timestamp vs timestamptz
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
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.
They both use 8 bytes for storage so there is no advantage in terms of storage of using timestamp vs timestamptz.
Set the Timezone
To set the timezone for use with timestamptz you can utilize a query like this:
SET timezone = 'America/Los_Angeles';
Show the Timezone
You can view which timezone is currently being used with this command:
postgres=# SHOW timezone;
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.
CREATE TABLE demo (t TIMESTAMP, ttz TIMESTAMPTZ);
Now to insert data into the table:
INSERT INTO demo (t, ttz)
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:
t | ttz
2019-10-11 12:11:24 | 2019-10-11 14:11:24-05
As you can see now the two times are different because
ttz has taken our timezone into account.
We hope you understand the difference between PostgreSQL timestamp vs timestamptz more clearly now. Thank you for joining us for this demo!
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started