AWS Database Blog

Converting the SYSDATE function from Oracle to PostgreSQL

July 2023: This post was reviewed for accuracy.

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 function. SYSDATE is the most commonly used date/time function in applications or stored procedures and triggers. Tables with columns like creation_date, last_updated_date, 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 SYSDATE.

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:

  • CURRENT_DATE
  • CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
  • CURRENT_TIME/CURRENT_TIME(precision)
  • LOCALTIME/LOCALTIME(precision)
  • LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

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

The CURRENT_DATE function shows the current date in yyyy-mm-dd format. See the following code:

postgres=> select CURRENT_DATE;
 current_date
--------------
 2020-01-03
(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision) and CURRENT_TIME/CURRENT_TIME(precision)

The functions CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision) and 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:

postgres=> select CURRENT_TIMESTAMP;
       current_timestamp      
-------------------------------
 2020-01-03 04:38:15.662514+00
(1 row)
 
postgres=> select CURRENT_TIMESTAMP(2);
     current_timestamp    
---------------------------
 2020-01-03 04:38:19.75+00
(1 row)
 
postgres=> select CURRENT_TIME;
    current_time   
--------------------
 04:40:29.409115+00
(1 row)
 
postgres=> select CURRENT_TIME(2);
  current_time 
----------------
 04:40:38.01+00
(1 row)

LOCALTIME/LOCALTIME(precision) and LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

The functions LOCALTIME/LOCALTIME(precision) and 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:

postgres=> select LOCALTIMESTAMP;
       localtimestamp      
----------------------------
 2020-01-03 04:42:39.405423
(1 row)
 
postgres=> select LOCALTIMESTAMP(2);
     localtimestamp    
------------------------
 2020-01-03 04:42:41.97
(1 row)
 
postgres=> select LOCALTIME;
    localtime   
-----------------
 04:42:24.022253
(1 row)
 
postgres=> select LOCALTIME(2);
  localtime 
-------------
 04:42:32.01
(1 row)

Supported non-SQL standard functions

The following are supported non-SQL standard functions:

  • transaction_timestamp()
  • statement_timestamp()
  • clock_timestamp()
  • timeofday()
  • now()

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()

The 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.

statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. See the following code:

postgres=> begin;
BEGIN
postgres=> select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 04:58:39.271915+00 | 2020-01-03 04:58:37.690723+00
(1 row)
 
postgres=> select pg_sleep(5);
 pg_sleep
----------
 
(1 row)
 
 
postgres=> select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 04:58:49.770003+00 | 2020-01-03 04:58:37.690723+00
(1 row)

clock_timestamp() vs statement_timestamp()

The 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:

postgres=> WITH time_test
postgres->      AS (SELECT Statement_timestamp())
postgres-> SELECT *,
postgres->        Pg_sleep(3) AS "<- see the difference ->",
postgres->        Statement_timestamp()
postgres-> FROM   time_test;
      statement_timestamp      | <- see the difference -> |      statement_timestamp     
-------------------------------+--------------------------+-------------------------------
 2020-01-03 05:05:08.458192+00 |                          | 2020-01-03 05:05:08.458192+00
(1 row)
 
postgres=>
postgres=> WITH time_test
postgres->      AS (SELECT clock_timestamp())
postgres-> SELECT *,
postgres->        Pg_sleep(3) AS "<- see the difference ->",
postgres->        clock_timestamp()
postgres-> FROM   time_test;
        clock_timestamp        | <- see the difference -> |        clock_timestamp       
-------------------------------+--------------------------+-------------------------------
 2020-01-03 05:05:18.040189+00 |                          | 2020-01-03 05:05:21.042861+00
(1 row)

timeofday() vs clock_timestamp()

The functions timeofday() and 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 timeofday:

postgres=> select clock_timestamp(), pg_typeof(clock_timestamp()), timeofday(), pg_typeof(timeofday());
        clock_timestamp        |        pg_typeof         |              timeofday              | pg_typeof
-------------------------------+--------------------------+-------------------------------------+-----------
 2020-01-03 05:28:50.203961+00 | timestamp with time zone | Fri Jan 03 05:28:50.203961 2020 UTC | text
(1 row)

now()

The now() function is a traditional PostgreSQL equivalent to transaction_timestamp(). In the following code example, both functions show the same timestamp:

postgres=> begin;
sBEGIN
postgres=> select now(), transaction_timestamp();
              now              |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00
(1 row)
 
postgres=> select pg_sleep(3);
 pg_sleep
----------
 
(1 row)
 
postgres=> select now(), transaction_timestamp();
              now              |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00
(1 row) 

Choosing which PostgreSQL function to use

The Oracle 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:

SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
dbms_lock.sleep(30);
DBMS_OUTPUT.PUT_LINE('End : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Start : 2020-01-03 06:11:06
End : 2020-01-03 06:11:36

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 SYSDATE:

DO
$BODY$
BEGIN
RAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();
RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();
RAISE NOTICE 'now()                   : %', now();
RAISE NOTICE 'current_timestamp       : %', current_timestamp;
RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();
RAISE NOTICE '';
RAISE NOTICE 'sleep for 15 secs and see the difference below: %', pg_sleep(15);
RAISE NOTICE '';
RAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();
RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();
RAISE NOTICE 'now()                   : %', now();
RAISE NOTICE 'current_timestamp       : %', current_timestamp;
RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();
END;
$BODY$;

The following code is the result:

NOTICE:  clock_timestamp()       : 2020-01-03 06:20:52.3715+00
NOTICE:  statement_timestamp()   : 2020-01-03 06:20:52.371345+00
NOTICE:  now()                   : 2020-01-03 06:20:52.371345+00
NOTICE:  current_timestamp       : 2020-01-03 06:20:52.371345+00
NOTICE:  transaction_timestamp() : 2020-01-03 06:20:52.371345+00
NOTICE: 
NOTICE:  sleep for 15 secs and see the difference below:
NOTICE: 
NOTICE:  clock_timestamp()       : 2020-01-03 06:21:07.438274+00
NOTICE:  statement_timestamp()   : 2020-01-03 06:20:52.371345+00
NOTICE:  now()                   : 2020-01-03 06:20:52.371345+00
NOTICE:  current_timestamp       : 2020-01-03 06:20:52.371345+00
NOTICE:  transaction_timestamp() : 2020-01-03 06:20:52.371345+00

Only the 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 SYSDATE() and 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 SYSDATE results:

SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union
     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual);
 
CONFIG OFFSET
--------------- ------------------------------
dbtimezone      +00:00
sessiontimezone +05:30
 
SQL> select sysdate from dual;
 
SYSDATE

03/01/2020 09:56:53
 
SQL> alter session set time_zone = ‘-08:30’;
Session altered.
 
SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union
     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual);
 
CONFIG OFFSET
--------------- ------------------------------
dbtimezone       +00:00
sessiontimezone. -08:30
 
SQL> select sysdate from dual;
 
SYSDATE

03/01/2020 09:57.34

After changing the session time zone, SYSDATE still returns the server timestamp irrespective of the client or session time zone setting.

Effects in PostgreSQL

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:

postgres=> show timezone;
 TimeZone

 UTC
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       

 2020-01-03 06:25:36.165378+00
(1 row)
 
postgres=> set timezone = ‘America/New_York’;
SET
postgres=> show timezone;
     TimeZone    

 America/New_York
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       

 2020-01-03 01:25:49.329555-05
(1 row)
 
postgres=>

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.

DST considerations

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.

The 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:

postgres=> select n.name, n.abbrev N_abbrev,a.abbrev, n.utc_offset N_utc_offset , 
                 a.utc_offset, n.is_dst N_is_dst, a.is_dst
  from pg_timezone_names n, pg_timezone_abbrevs a
 where n.name = a.abbrev
   and n.utc_offset <> a.utc_offset
 order by 1;
 
name | n_abbrev | abbrev | n_utc_offset | utc_offset | n_is_dst | is_dst
------+----------+--------+--------------+------------+----------+--------
CET | CEST | CET | 02:00:00 | 01:00:00 | t | f
EET | EEST | EET | 03:00:00 | 02:00:00 | t | f
MET | MEST | MET | 02:00:00 | 01:00:00 | t | f
WET | WEST | WET | 01:00:00 | 00:00:00 | t | f
(4 rows)
 
postgres=> show timezone;
 TimeZone
----------
 UTC
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       
-------------------------------
 2020-01-03 06:29:09.672859+00
(1 row)
 
postgres=> set session time zone 'MET';
SET
postgres=> select clock_timestamp() AT TIME ZONE 'MET';
          timezone         
----------------------------
 2020-01-03 07:29:16.261098
(1 row)

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:

postgres=> select clock_timestamp() AT TIME ZONE 'UTC' + interval '02:00:00';
          ?column?         
----------------------------
 2020-01-03 08:29:19.732955
(1 row)

For the MET time zone, the correct time zone should be Europe/Berlin. See the following code:

postgres=> select * from pg_timezone_names where lower(name) like '%berlin%';
 
 name          | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CEST   | 02:00:00   | t

To validate if DST affects the time zone, complete the following steps:

  1. Find historical or upcoming DST changes. For more information, see Upcoming Daylight Saving Time Clock Changes.
  2. In Oracle, check if the UTC offset changes just before and after the DST change.
  3. 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.

SQL> ALTER SESSION SET TIME_ZONE='UTC';
Session altered.
 
-- Before DST, 28-OCT-2018 at 00:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET
 
SQL> select to_timestamp('2020-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual;
 
TO_TIMESTAMP('2018-10-2800:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'
---------------------------------------------------------------------------
28-OCT-18 02.00.00.000000000 AM MET
 
-- Before DST, 28-OCT-2018 at 01:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET
 
SQL> select to_timestamp('2018-10-28 01:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual;
 
TO_TIMESTAMP('2018-10-2801:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'
---------------------------------------------------------------------------
28-OCT-18 02.00.00.000000000 AM MET

The following code shows the same effect in PostgreSQL:

postgres=> show timezone;
TimeZone
----------
UTC
 
postgres => select '2018-10-28 00:00:00' AT TIME ZONE 'Europe/Berlin';
timezone
---------------------
2018-10-28 02:00:00
 
postgres => select '2018-10-28 01:00:00' AT TIME ZONE 'Europe/Berlin';
timezone
---------------------
2018-10-28 02:00:00

Recommendation

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:

CREATE OR REPLACE FUNCTION <<Your schema>>.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
    SELECT clock_timestamp() AT TIME ZONE '<<DB Timezone>>';
$BODY$
LANGUAGE sql;

In the following code example, a change to the client-side time zone setting returns a consistent result:

CREATE OR REPLACE FUNCTION public.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
   SELECT clock_timestamp() AT TIME ZONE 'Europe/Berlin';
$BODY$ 
LANGUAGE sql;
 
postgres=> set session time zone 'UTC';
SET
postgres=>  select sysdate();
          sysdate          
----------------------------
 2020-01-03 07:34:54.441904
(1 row)
 
postgres=> set session time zone 'Asia/Kolkata';
SET
postgres=> select sysdate();
          sysdate          
----------------------------
 2020-01-03 07:35:02.392743
(1 row)

Summary

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.