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:
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.
SQL Server version 2019 introduced the CURRENT_TIMEZONE() function that returns the value of the time zone:
select current_timezone()
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.
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;
On the same instance, if I use SYSTIMESTAMP that returns the timestamp complete with time zone of the system:
select systimestamp from dual;
We see a different result, if we use the SYSDATE function:
select to_char(sysdate, 'dd/mm/yyyy hh:mi:ss') from dual;
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;
PostgreSQL
In Postgres we can retrieve the date time with time zone using the CURRENT_TIMESTAMP function:
select current_timestamp;
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';
The same can also be achieved using:
show timezone;
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
So, checking again our current date time and time zone:
select SYSDATETIMEOFFSET()
We’d like to convert it to “Pacific Standard Time”:
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time';
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;
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;
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!
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;
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;
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;
And also check the time zone:
show timezone;
Checking the date time of the server we can see that it’s still in UTC:
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;
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
- As usual links to the official documentation:
- Oracle: Oracle Datetime Data Types and Time Zone Support
- PostgreSQL: PostgreSQL Datetime Data Types and Time Zone Support
- Some links to other tips regarding time zones:
