Date Functions like now(), current_timestamp etc. Don't change the Values Dynamically when run within a Function

Products

• VMware Tanzu Greenplum
• Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

Passing date/time functions like now(), current_date, current_timestamp etc doesn't change dynamically within the function and due to this the query give wrong results.

Let's take the below function, for example, the date function supplies the values to the variable:

create or replace function check_timestamp_1() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp;
output varchar;
begin
 	now1 := now();
 	current_time1 := current_timestamp;
 	output := 'Now: '|| now1 || ' current timestamp: ' || current_time1 ;
        return output ;        
end;
$BODY$
language 'plpgsql';

So, each call of the function within the session results in having the same values when it should be dynamic:

flightdata=# select check_timestamp_1();

								check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

flightdata=# select now();
              now
-------------------------------
 2015-02-20 04:05:53.556095-08

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

Environment

Cause

current_timestamp is parsed into the function call now() and now() is a stable function, check the Postgres documentation for more information on the function volatility categories.

Also, this was a feature in postgres and left this way to avoid multiple transactions having different values when calling the date function.

 

Resolution

Here are some of the solution to overcome the problem.

-- Place the function in a select to execute each time in order to retrieve the value

create or replace function check_timestamp_2() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp;
output varchar;
begin
 	now1 := (select now());
 	current_time1 := (select current_timestamp);
 	output := 'Now: '|| now1 || ' current timestamp: ' || current_time1 ;
        return output ;        
end;
$BODY$
language 'plpgsql';

-- Or, use the timeofday() function, which is a violate function. When used with the different cast it can yield the desired results

create or replace function check_timestamp_3() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp with time zone;
current_date1 date;
output varchar;
begin
 	now1 := timeofday()::timestamp;
 	current_time1 := timeofday()::timestamp with time zone;
 	current_date1 := timeofday()::date;
 	output := 'Now: '|| now1 ||' current timestamp: ' || current_time1 ||' current date: ' || current_date1 ;
        return output ;       
end;
$BODY$
language 'plpgsql'; 

-- Or, disconnect and reconnect after each executes, which might not be feasible always

flightdata=# \q
[gpadmin@mdw pg_log]$ psql
psql (8.2.15)
Type "help" for help.

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:07:08.775462 current timestamp: 2015-02-20 04:07:08.775462
(1 row) 

Additional Information

VMwarel Greenplum Database (GPDB) all versions