How to Convert Timezone in PostgreSQL Server

1. Convert Timezone in PostgreSQL Server
2. the timezone() Function and Its Uses to Convert Timezone
3. Use SQL Queries to Convert Timestamp Values to Different Timezones
4. Important SQL Statements to Convert Timezone

Would you like to convert the timestamp values stored in your table to a different timezone in PostgreSQL? This article describes a procedure to convert the stored timestamp values to a different timezone.

Convert Timezone in PostgreSQL Server

There are different ways of converting one timestamp value to another timezone. There are two ways that we will discuss in this article:

1. Use of the timezone() function
2. Use of the SQL queries

We can follow any of them to convert the timestamps from one timezone to another.

the timezone() Function and Its Uses to Convert Timezone

The PostgreSQL server offers numerous user functions that help them perform tasks quickly and efficiently. However, before using the timezone() function, let’s discuss it in detail.

The timezone() function is used to convert one timestamp value to another timezone. The syntax for the timezone() function is as follows:

timezone(zone, timestamp)

As shown above, the timezone() function takes two arguments. The first argument, zone, represents the timezone in which you want to convert the timestamp argument.

The second argument, timestamp, represents the timestamp value that you want to convert to a different timezone.

The return value of the timezone() function varies depending upon the timestamp value. The function will return a different value if the original timestamp value includes a timezone.

On the other hand, the returned value will be different if the timestamp value DOES NOT have a timezone. Let’s understand it with code examples below.

Example Codes Containing Timestamp With Timezone

The examples under this section use the timezone() function, where the timestamp argument includes the timezone. Here is an SQL statement in PostgreSQL:

SELECT timezone('PST', timestamp with time zone '2020-10-25 00:00:00+00');

The timezone() function converts the timestamp value provided in the second argument to the specified timezone specified in the first argument.

The return value is without a timezone. Hence, the result of the query mentioned above is as follows:

This example changes the timezone of the input timestamp value:

SELECT timezone('PST', timestamp with time zone '2020-10-25 00:00:00+01');

Output:

Suppose you use the timezone() function with the argument "timestamp with timezone"and do not specify the timezone with the timestamp. Then, the original timestamp in the argument will be converted to a local timezone.

SELECT timezone('PST', timestamp with time zone '2020-10-25 00:00:00');

Output:

We can see that the returned timestamp is 11 hours ahead, which means my local timezone is 11 hours behind the PST timezone.

If you want to see the timezone offset that has been used, execute the following query:

SELECT timestamp with time zone '2020-10-25 00:00:00';

Output:

Example Codes Containing Timestamp Without Timezone

We can execute the same examples with the argument "timestamp without timezone". The result converts the original timestamp using the current timezone setting, even if the timestamp includes a timezone offset.

The return value from the function contains a timezone offset appended to it. Here is a query in PostgreSQL that does not include a timezone offset:

SELECT timezone('PST', timestamp without time zone '2020-10-25 00:00:00+00');

Hence, the result of the query mentioned above is as follows:

The following example includes a timezone offset:

SELECT timezone('PST', timestamp without time zone '2020-10-25 00:00:00+12');

The result of the above query is the same as a result achieved without a timezone offset:

To understand the concept of the "timestamp without timezone" argument, see the query below:

SELECT timestamp without time zone '2020-10-25 00:00:00+12';

Output:

Note: The timezone() function can be used with numerous arguments. Try using the timezone() function with localtimestamp or current_timestamp arguments. The argument “timestamp with/ without timezone” can also be used with time values: “time with/ without timezone.”

Use SQL Queries to Convert Timestamp Values to Different Timezones

Here, we discuss a few SQL statements we can execute in PostgreSQL to convert the timestamp values to another timezone without a function.

The first query selects a timestamp and a timezone. The conversion using an SQL statement is shown below:

SELECT (timestamp'2020-10-25 00:00:00') AT TIME ZONE 'PST';

Output:

Note: You can execute the queries with any of the required time zones. For simplicity, the timezone used throughout the article is PST.

Here is a generalized query to convert timestamp values to another timezone:

SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'PST') AS local_timestamp FROM my_table;

To explain the query in simpler words, you can choose a stored_timestamp attribute from a table named my_table in PostgreSQL.

The stored_timestamp timezone is provided alongside it, and the following timezone represents the timezone you want to convert the timestamp. The result will be named local_timestamp.

Another statement that converts a timestamp value to a different timezone is as follows:

SELECT '2020-10-25 00:00:00'::timestamp AT time zone 'PST';

It produces the same result as query 1:

The following query is an alternative to the queries mentioned above. If unsure of your timezone, you can convert the timestamp to that timezone using a zone interval.

Note: The query below does not depict the zone interval of PST.

SELECT '2020-10-25 00:00:00' :: timestamp AT time zone INTERVAL'+08:30';

The timestamp received for the zone interval "+08:30" is as follows:

Important SQL Statements to Convert Timezone

To help you convert a timestamp value to a different timezone value, here are two essential SQL statements that you can use in PostgreSQL.

The following statement returns your current time zone. Hence, if you are not sure of your timezone, run this statement:

SHOW timezone;

If you are interested in having a look over the different timezones that the PostgreSQL server supports, use the following statement:

SELECT * FROM pg_timezone_names;

So, converting a timestamp value to another timezone is not a difficult task. Simple SQL statements in PostgreSQL can help you transform the timestamp value to a timezone of your choice.