Sometimes interesting problems come out of processes that appear to be basic, boring tasks. Database migrations are a good example, especially if the migration requires a re-working of datetime calculations between one RDBMS and another. What may be taken for granted in the source RDBMS may produce incorrect results in the destination RDBMS when datetime arithmetic and limitations for some data types are not fully understood. Let’s look into that and, using a real-life example, see what can go wrong.
Oracle, for decades, has made datetime arithmetic basic and, well, simple; using SYSDATE it’s incredibly easy to create dates in the future as well as in the past:
SQL> alter session set nls_date_Format = 'DD-MON-RRRR HH24:MI:SS';
Session altered.
SQL> select sysdate, sysdate+10, sysdate-7 from dual; SYSDATE SYSDATE+10 SYSDATE-7 -------------------- -------------------- -------------------- 26-APR-2022 09:47:01 06-MAY-2022 09:47:01 19-APR-2022 09:47:01 SQL>
In one SQL statement we have reported the current datetime, a datetime in the future and one in the past, all with simple addition and subtraction and notice that the time portion of the datetime value is also supplied. Other RDBMS engines are not as forgiving; Postgres requires that the datetime be converted to an interval prior to finding the difference between two datetime values, so that the date and the time elements are properly reported. A simple datetime difference in Oracle is coded:
select sysdate – expiry_date from expiry;
which returns the difference, including the fractional part of a day, which is fairly easily converted to days, hours and minutes with some basic mathematics:
SQL> select trunc(expiry_dt - sysdate) days,
2 trunc(((expiry_dt - sysdate) - trunc(expiry_dt - sysdate))*24) hours,
3 trunc(((expiry_dt - sysdate) - trunc(expiry_dt - sysdate))*1440)-
4 trunc(((expiry_dt - sysdate) - trunc(expiry_dt - sysdate))*24)*60 minutes
5 from expiry
6 where
7* trunc(expiry_dt - sysdate) < 100
SQL> /
DAYS HOURS MINUTES
---------- ---------- ----------
26 23 47
53 23 47
80 23 47
SQL>
(I said basic, not short.) The only mathematical “gyrations” needed involve getting the fractional part of the day isolated and then getting that converted to hours and minutes with basic multiplication. No conversion of any date values to, say, intervals, was necessary to return the days and fraction thereof.
Postgres can do date subtraction, however if the date value is not converted to an interval with the proper time units it produces integer differences, meaning the time portion of the difference is “lost”. To address that the desired interval can be added to the datetime provided by now() or current_timestamp (minutes, seconds, hours) to return a more granular result. Executing a pg_sleep() within the plpgsql block doesn’t affect calls like current_timestamp and now() as the value from the initial call is preserved in the block context. As an example two functions are created, one that modifies a datetime value by adding an interval and one that simply relies upon the delay from a pg_sleep() call to provide a more recent time value; the code is provided below:
drop function test;
drop function test2;
set client_min_messages = notice;
CREATE FUNCTION test (nonsense integer)
RETURNS interval AS $$
DECLARE
fst_date timestamp;
scd_date timestamp;
BEGIN
fst_date := now();
raise notice 'First date: %',fst_date;
scd_date := now() + interval '60 second';
raise notice 'Second date: %',scd_date;
RETURN scd_date - fst_date;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION test2 (nonsense integer)
RETURNS interval AS $$
DECLARE
fst_date timestamp;
scd_date timestamp;
BEGIN
fst_date := now();
raise notice 'First date: %',fst_date;
perform pg_sleep(5);
scd_date := now();
raise notice 'Second date: %',scd_date;
RETURN scd_date - fst_date;
END;
$$ LANGUAGE plpgsql;
select test(1);
select test2(1);
The results from executing these functions are shown below — notice that subsequent calls to now() after a pg_sleep() execution (for 5 seconds) provide the same datetime value:
psql:/home/postgres/files/test.sql:38: NOTICE: First date: 2022-04-27 07:12:39.635446 psql:/home/postgres/files/test.sql:38: NOTICE: Second date: 2022-04-27 07:13:39.635446 test ---------- 00:01:00 (1 row) psql:/home/postgres/files/test.sql:39: NOTICE: First date: 2022-04-27 07:12:39.636306 psql:/home/postgres/files/test.sql:39: NOTICE: Second date: 2022-04-27 07:12:39.636306 test2 ---------- 00:00:00 (1 row)
[Longer values supplied to pg_sleep() provide the same results.]
A “raise notice” call, plus setting client_min_messages to notice, provides the NOTICE lines in the above output. Both functions call now() to set the datetime value; test also adds an interval of 60 seconds to the value returned by now() and does not call pg_sleep() to delay the second now() assignment. Function test2 uses only pg_sleep() in an attempt to get Postgres to return a more current value for now() — the results tell the tale. This can be confusing to those used to Oracle and its ability to use a dbms_lock.sleep() call to delay time and allow the RDBMS to return the current datetime when SYSDATE is called. When converting Oracle functions and procedures to PLPGSQL this can result in erroneous results as calculating datetime differences won’t ‘behave’ as Oracle developers expect. This is especially true when datetime differences in minutes or seconds are expected because the datetime calls won’t provide current results since the PLPGSQL context doesn’t appear to function that way. In certain cases an interval value can be used in a multiplication scenario to essentially convert the current datetime value to an interval type, providing hours, minutes and seconds when a datetime difference is calculated. Datetime values stored in table columns, when fetched, can undergo this transformation. Thorough testing is required to ensure that datetime differences within a Postgres installation provide the desired and expected results.
Code migrations between RDBMS engines can be daunting for a novice or moderately skilled developer as assumptions based on current behavior may not hold true in the destination RDBMS. Robust and rigorous testing should be done to “shake out” the code changes to ensure that correct functionality and results are provided. Failing to provide proper testing scenarios can cause issues “down the road” when migrated code doesn’t perform exactly as it did in the source implementation. Migrations take patience; when done properly that patience will be rewarded.
Put that code over here…
Oracle Tips and Tricks -- David Fitzjarrell 