Introduction to PostgreSQL Timestamp with Timezone
PostgreSQL timestamp with timezone is defined as showing timestamp with timezone, which we have defined to the database server. In PostgreSQL, there are two data types of timestamp available, i.e., timestamp with timezone and timestamp without a timezone; the timestamptz is defined as timestamp with timezone. Timestamptz is a zone-aware data type of date and time, the default timezone of the PostgreSQL database is UTC which means when we insert any value in the timestamptz data type column, it will automatically convert into UTC. When we query timestamptz to table the same time, the UTC value again converts back to the time. In this topic, we are going to learn about PostgreSQL Timestamp with Timezone.
Syntax
Below is the syntax of timestamp with timezone in PostgreSQL.
Create a table to define the data type of column as timestamp with timezone
Create table name_of_table (name_of_column1 data_type, name_of_column2 TIMESTAMPTZ, name_of_column3 data_type, …, name_of_columnN data_type);
Select timestamp with timezone
Select 'Date time':: timestamptz as "Select timestamp with time zone in PostgreSQL";
OR
select date time::timestamptz;
OR
SELECT 'Date time':: timestamptz at time zone 'timezone' as "name_of_timezone";
Set timezone
SET TIME ZONE ‘name_of_timezone’;
Below is the parameter description syntax of timestamp with timezone in PostgreSQL.
- Select –This is defined as a select timestamp with a timezone in PostgreSQL. We can select the same from the table column as well as from system time.
- Create table –create the table by defining data type as timestamp with time zone to the specified column.
- Name of the table – It is used to define the table name, which is uniquely identified from which table column we have defined data type as timestamp with the time zone in PostgreSQL.
- Name of the table – This is defined as the name of the column from which table column we have defining data type as timestamp with the time zone in PostgreSQL.
- Data type –This is defined as the defined data type of column at the time of table creation in PostgreSQL. We can define any data type to the column at the time of table creation.
- Set time zone –This is defined as a set time zone to the database server. We can set the time zone of the server by using the set time zone command.
- Date and time –We are using date and time at the time of setting timestamp with timezone in PostgreSQL.
How Timestamp with Timezone works in PostgreSQL?
Below is the working of timestamp with timezone in PostgreSQL.
We can use a timestamp data type in PostgreSQL by using it with and without the time zone. If we want to use a without time zone, we need to use a timestamp data type; if we want to use timestamp with a time zone, we need to use a timestamp data type.
We can see the timezone of the server by using the show timezone command. The below example shows that how to see server timezone in PostgreSQL are as follows.
showtime zone;
In the above example, the current timestamp with timezone is Asia/Kolkata; we can change the same by using the set time zone command.
There is an 1187 timezone available in PostgreSQL version 10. Below is the example of the timezone which was available in PostgreSQL.
select count(*) from pg_timezone_names;
select * from pg_timezone_names limit 10;
The above example shows that the pg_timezone_names catalog table contains all timezone names that were available in PostgreSQL version 10.
It will contain the timezone name and its abbreviation, which was used in PostgreSQL.
The timestamp with time zone data type storage size is 8 bytes. The below example shows the size of the timestamptz data type in PostgreSQL.
SELECT typname, typlen FROM pg_type WHERE typname ~ '^timestamptz';
PostgreSQL timestamp with timezone data type is stored in the UTC value. Therefore, the value of timestamp with time zone data type is changed after inserting the date and time into the table.
But timestamp without timezone data type value is not changed after inserting it into the table.
The example below shows that timezone is automatically added while defining the timestamptz data type to the column.
insert into timestamp_test values ('2016-06-22 19:10:25');
select * from timestamp_test;
\d+ timestamp_test;
Examples of PostgreSQL Timestamp with Timezone
Below is an example of timestamp with the time zone in PostgreSQL.
Example #1 – Create a table to define timestamptz data type to the column
The below example shows that create a table and define the timestamptz data type to the column.
We have defining timestamp with time zone data type to the date and date_time column.
create table timestamp_timezone (id int, date timestamptz, date_time timestamptz);
\d+ timestamp_timezone;
Example #2 – Insert date into the timestamptz datatype column
The below example shows that insert date into the timestamptz data type column.
insert into timestamp_timezone values (1, '2020-07-01 19:10:25', '2020-07-02 19:10:25');
insert into timestamp_timezone values (2, '2020-07-01 19:10:25', '2020-07-02 19:10:25');
select * from timestamp_timezone;
Example #3 – Change the timezone
The below example shows that change the timezone of the database in PostgreSQL.
In the below example, we have to change the timezone from Asia/Calcutta to UTC.
show timezone;
set time zone 'UTC';
show timezone;
Example #4 – Select timestamp with the timezone of date
The below example shows that select the timestamp with the timezone of date in PostgreSQL.
In the below example, we have selected timestamp with timezone for the date of ‘2020-07-01 19:10:25’.
SELECT '2020-07-01 19:10:25':: timestamptz as "Timestamp with timezone";
SELECT '2020-07-01 19:10:25':: timestamptz;
Conclusion
There are two types of timestamp data types available in PostgreSQL, i.e., timestamp and timestamptz. Timestamp with timezone is defined as a value of column with time zone format. There is an 1187 timezone available in PostgreSQL version 10. PostgreSQL database is used the UTC zone as a default time zone.
Recommended Articles
This is a guide to PostgreSQL Timestamp with Timezone. Here we discuss How Timestamp with Timezone works in PostgreSQL along with the Examples. You may also have a look at the following articles to learn more –
