Time zones in SQL Server, Oracle and PostgreSQL

By:   |   Updated: 2021-11-05   |   Comments   |   Related: More > Other Database Platforms


   Free MSSQLTips whitepaper - "Calculating Costs for Microsoft SQL Server" - download now

Problem

If you work in a multinational environment, it’s highly probable that you have to deal with different time zones and with servers/databases set in different time zones. Unfortunately, when it comes to time zones we have some differences between our SQL Server, Oracle and PostgreSQL both in the data types involved and in the way the date/time is retrieved.

Solution

In this tip we will review the various differences on time zones between SQL Server, Oracle and PostgreSQL as well as the different Date and Time data types involved and we will see the correct way to set and change the instance time zone. As always, we will use the GitHub freely downloadable database sample Chinook, as it is available in multiple RDBMS formats. It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for data structure and all the insert statements for data.

Time Zone Data Types

First of all, a recap of just the data types with time zone that are available in the 3 RDBMS:

table of database time zone data types

Get Time Zone Information

Now let’s see the different ways we have to see the time zone.

SQL Server

As we have seen in my previous tip on date functions and operations SQL Server, Oracle and PostgreSQL Date Functions the actual date time can be retrieved with the GETDATE() function, but in order to get the time zone we must use:

select SYSDATETIMEOFFSET()

This function returns the current date and time as well as the time zone offset.

query results

SQL Server version 2019 introduced the CURRENT_TIMEZONE() function that returns the value of the time zone:

select current_timezone()
query results

Please note that the same result can be obtained in previous versions of SQL Server with the following query:

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone as TimeZone

As you can see the time zone can be retrieved directly from the host server, as host and instance share the same time zone.

query results

Oracle

On Oracle things get a little bit more complicated, first of all the function to use to get the time zone of the instance is DBTIMEZONE:

select DBTIMEZONE from dual;
query results

On the same instance, if I use SYSTIMESTAMP that returns the timestamp complete with time zone of the system:

select systimestamp from dual;
query results

We see a different result, if we use the SYSDATE function:

select to_char(sysdate, 'dd/mm/yyyy hh:mi:ss') from dual;
query results

The reason for these results is that all these functions retrieve the date/time directly from the system, i.e. the host server, just like we’ve seen in SQL Server. The date/time and time zone is one set at the OS level on the host server regardless of what is set at the database instance level.

In Oracle we have the possibility to see the local time zone of the session, using the SESSIONTIMEZONE function:

select sessiontimezone from dual;
query results

PostgreSQL

In Postgres we can retrieve the date time with time zone using the CURRENT_TIMESTAMP function:

select current_timestamp;
query results

And we can check the instance time zone querying the settings of the PostgreSQL cluster (remember the different naming convention for PostgreSQL, please refer to my tip on that Relational Database Comparison between SQL Server, Oracle and PostgreSQL):

select name, setting from pg_settings where name='TimeZone';
query results

The same can also be achieved using:

show timezone;
query results

As with Oracle the session can have a different time zone in respect to the PostgreSQL Cluster, again more on that later.

How to Change Time Zone Settings

So, now let’s see how we can change the time zone settings and how we can obtain our date time in another time zone different from the default one of the system.

SQL Server

In SQL Server the date time (and time zone), as I already hinted, is derived directly from the operating system of the host server, so it is impossible to change it just for the instance. In order to change the time zone of SQL Server you need to change it at the operating system level, changing the date time of the server.

However, if this is not feasible, there is the possibility, from SQL Server 2016 onwards, to use the function AT TIME ZONE that converts a date time in the specified time zone, let’s see an example.

First of all let's list all available time zones in the system using sys.time_zone_info:

select * from sys.time_zone_info
query results

So, checking again our current date time and time zone:

select SYSDATETIMEOFFSET()
query results

We’d like to convert it to "Pacific Standard Time":

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time';
query results

Please note that the function needs the input date with information of the date time offset, otherwise it won’t be able to perform the time zone conversion.

Oracle

Also in Oracle, as already explained above, the date time is retrieved at the operating system level, so in order to change time zone it must be changed on the host server. Nonetheless it is possible in Oracle to change the database time zone, anyway the database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE data types.

So first of all, let’s retrieve the list of available time zone in the system:

SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES;
query results

Then we change the database time zone to the same one we used on SQL Server, unfortunately here in Oracle the name is different as it’s America/Los_Angeles or abbreviated as PST:

alter database set time_zone='America/Los_Angeles';

The database should then be restarted and if we check we can see the change:

select DBTIMEZONE from dual;
query results

But if we issue this query:

select current_timestamp from dual;

We end up again with the same results as before: that is because CURRENT_TIMESTAMP is retrieved at the session level!

query results

But, as I started to hint before, in Oracle we can setup a different time zone also at the session level using the ALTER SESSION statement:

alter session set time_zone='America/Los_Angeles';

Let’s check it:

select current_timestamp from dual;
query results

PostgreSQL

In PostgreSQL instead we can setup the time zone of the database independently from the system. Let’s see how, first we will query for a list of available time zones:

select name, abbrev from pg_timezone_names;
query results

So as with SQL Server and Oracle we want to set the database to the time zone of the Pacific coast of the US, and here we have a different name:

alter system set TimeZone='US/Pacific';

At this point we need to restart PostgreSQL, or reload the configuration with this command:

SELECT pg_reload_conf();

We can now check the time date:

select current_timestamp;
query results

And also check the time zone:

show timezone;
query results

Checking the date time of the server we can see that it’s still in UTC:

system time

Like in Oracle we have anyway the possibility to change the time zone at the session level, issuing a simple SET TIMEZONE command:

set TimeZone='Europe/Berlin';

We can now check:

select current_timestamp;
query results

Obviously, all changes made at session level are lost as soon as the session is disconnected, both in Oracle and PostgreSQL.

Summary

  • In this tip we have seen that in SQL Server and Oracle the time zone is set and taken by the most used date time functions at the host server level.
  • PostgreSQL can have a different time zone than the host and that is the date time retrieved by all functions.
  • Both Oracle and PostgreSQL have the possibility to set a different time zone at the session level and SQL Server can return a date time in a different time zone.
  • Last but not least, we have seen how to retrieve the list of time zone names available in the 3 database systems and that they all differ from each other!
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Andrea Gnemmi Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

View all my tips


Article Last Updated: 2021-11-05

Comments For This Article





download














get free sql tips
agree to terms