Converting the SYSDATE function from Oracle to PostgreSQL
An Oracle to PostgreSQL migration in the AWS Cloud can be a complex, multistage process with different technologies and skills involved, starting from the assessment stage to the cutover stage. For more information about the migration process, see Database Migration—What Do You Need to Know Before You Start?, Migration process and infrastructure considerations, Source database considerations, and Target database considerations for the PostgreSQL environment.
One of the most common issues while migrating from Oracle to Amazon RDS or Amazon Aurora PostgreSQL is with the
SYSDATE is the most commonly used date/time function in applications or stored procedures and triggers. Tables with columns like
approved_date are updated as
SYSDATE values by having default values of the column as
SYSDATE or by defining the trigger to insert or update column values as
This post shows an alternative to the Oracle
SYSDATE function for your PostgreSQL database.
There are three different aspects to consider when evaluating the date/time function for Oracle and PostgreSQL:
- Statement-level or transaction-level time
- Effect of client side-settings in time functions
- Daylight Saving Time (DST) consideration
Overview of PostgreSQL date/time functions
PostgreSQL provides several functions that return values related to the current date and time. Some of these functions are SQL standard, and others are non-SQL standard functions.
Supported SQL standard functions
The following are supported SQL standard functions:
These functions return the start time of the current transaction. There is no change in the value if you run these functions multiple times in the same transaction. This behavior is intentional and considered as a feature. If you want a consistent timestamp throughout the transaction, you can use these functions.
CURRENT_DATE function shows the current date in yyyy-mm-dd format. See the following code:
CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision) and CURRENT_TIME/CURRENT_TIME(precision)
CURRENT_TIME/CURRENT_TIME(precision) return the current date and time with a time zone. You can optionally use the precision parameter for this function. This precision rounds fractional digits (milliseconds). See the following code:
LOCALTIME/LOCALTIME(precision) and LOCALTIMESTAMP/LOCALTIMESTAMP(precision)
LOCALTIMESTAMP/LOCALTIMESTAMP(precision) return the current date and time without a time zone. You can optionally use the precision parameter for this function. This precision rounds fractional digits (milliseconds). See the following code:
Supported non-SQL standard functions
The following are supported non-SQL standard functions:
PostgreSQL also provides functions that return the start time of the current statement and the actual current time when the function is called.
transaction_timestamp() and statement_timestamp()
transaction_timestamp function behaves the same as
current_timestamp. However, as the name suggests, it returns the start time of a transaction and is consistent throughout the transaction.
statement_timestamp function returns the start time of the statement irrespective of transaction.
transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. See the following code:
clock_timestamp() vs statement_timestamp()
clock_timestamp() function returns the actual current time, so its value changes within a single SQL command.
The following code example demonstrates that
clock_timestamp returns different timestamp values in the same command but
statement_timestamp() returns the same values:
timeofday() vs clock_timestamp()
clock_timestamp() both behave in same way; the only difference is
timeofday() returns the text data type and
clock_timestamp() returns the timestamp with a time zone. In the
pg_typeof column in the following code example, it shows timestamp with time zone for the
clock_timestamp() function and text for
now() function is a traditional PostgreSQL equivalent to
transaction_timestamp(). In the following code example, both functions show the same timestamp:
Choosing which PostgreSQL function to use
SYSDATE function returns the DB server date/time at the time of statement execution. So, in a long-running transaction, if you have multiple
SYSDATE functions, every statement execution returns a different time.
In the following Oracle code example, you can see two different values for
SYSDATE function execution. The Oracle
SYSDATE function returns the start time of the statement irrespective of when the transaction starts:
In the following PostgreSQL code example, with a single transaction you can capture the time return by different PostgreSQL date and time functions at different intervals. The following code sleeps for 15 seconds in between to compare the results before and after. The output values to determine which function gives the same behavior as
The following code is the result:
clock_timestamp() function returns different timing information in a single transaction. Therefore, the closest match for an Oracle
SYSDATE function alternative for PostgreSQL is
clock_timestamp(). But this is not enough, because these values are just timestamps. Because different time zones have different timestamp values at the same point in time, you must also consider the time zone and DST. Otherwise, you may see different values than expected.
Effects of client-side time zone setting
Oracle date/time functions, such as
SYSTIMESTAMP(), return the current date and time of the database server time zone irrespective of the client or session time zone settings. But PostgreSQL date/time functions return the time as per your client or session time zone settings. In PostgreSQL, the timestamps with time zone values are stored internally in UTC and converted to local time in the zone specified by the time zone configuration parameter when displayed to the client.
Effects in Oracle
In Oracle, there is no effect to the
SYSDATE function with respect to the client- or session-level time zone setting. In the following code example, changing the time zone setting did not affect the
After changing the session time zone,
SYSDATE still returns the server timestamp irrespective of the client or session time zone setting.
Effects in PostgreSQL
clock_timestamp() and other time functions return the current date and time of the client session time zone. See the following code example:
If these timestamps are returned by different clients in different time zones and stored in a
TIMESTAMP WITHOUT TIME ZONE column, the data is misleading.
Implementation of the time zone name and offset don’t match between Oracle and PostgreSQL databases. You may not get the matching time zone name or time offset from UTC. Additionally, the behavior of the time zone setting in PostgreSQL depends on if you use the full time zone name or a time zone abbreviation. The time zone abbreviation in PostgreSQL defines a particular offset from UTC, but full time zone names can imply a set of DST date rules.
pg_timezone_names catalog table contains details with full time zones names, and
pg_timezone_abbrevs has the time zone abbreviations.
Example: Migrating an Oracle database
In the following example, you must migrate an Oracle database (DB time zone MET) to a PostgreSQL database, in which the application code uses
SYSDATE. You need a similar time function in PostgreSQL.
In Oracle, the MET time zone is DST aware and the UTC offset is +02:00:00. PostgreSQL has the time zone name MET (UTS offset +02:00:00 and DST aware) and also has the time zone abbreviation MET (UTC offset +01:00:00 and DST unaware).
In the PostgreSQL database, when you set the time zone in the session level or in
AT TIME ZONE, if there is a matching full name and abbreviation, it uses the abbreviation. If you set MET as the
timezone DB parameter, the offset is +01:00:00 and DST is not effective.
The following code example has matching time zone names and abbreviations with different offsets:
When you set the session time zone to MET, the time difference between UTC and MET is 1 hour, which is the offset related to the time zone abbreviation. Also, this time zone is not DST aware. You can check by adding intervals manually with the following code:
For the MET time zone, the correct time zone should be Europe/Berlin. See the following code:
To validate if DST affects the time zone, complete the following steps:
- Find historical or upcoming DST changes. For more information, see Upcoming Daylight Saving Time Clock Changes.
- In Oracle, check if the UTC offset changes just before and after the DST change.
- In PostgreSQL, check if the UTC offset changes just before and after the DST change.
Example: DST changes
In the following example, a DST change occurs for MET (Middle European Time) on 28-OCT-2018 at 03:00:00 A.M when local clocks go back 1 hour. The original DST UTC offset should be 02:00:00 and the new DST UTC offset should be 01:00:00.
In the following Oracle example code, the clock is set 1 hour back at the time of this DST change.
The following code shows the same effect in PostgreSQL:
After you consider the different aspects of PostgreSQL date and time functions (like the effect of client-side time zone settings and DST changes), you can use the following function to simulate the Oracle
SYSDATE function for PostgreSQL. It provides a statement-level timestamp, with no side effect on client-side settings:
In the following code example, a change to the client-side time zone setting returns a consistent result:
This post discussed the date/time-related functions available in Amazon RDS and Aurora PostgreSQL (see “Overview of PostgreSQL date/time functions” section for more details) , and determined that the
clock_timestamp() function is the closest match to the
SYSDATE function in Oracle. You can create the function based on
clock_timestamp with your Oracle database server time zone as shown in “Recommendation” section.
About the Authors
Baji Shaik is a Consultant with the AWS ProServe team in India. His background spans a wide depth and breadth of expertise and experience in SQL/NoSQL database technologies. He has architectured and designed many successful database solutions addressing challenging business requirements. He has provided solutions using PostgreSQL for reporting, business intelligence, data warehousing, applications, and development support. He also has a good knowledge of automation, orchestration, and DevOps in cloud environments.
Sudip Acharya is a Consultant with the AWS ProServe team in India. He works with internal and external Amazon customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.