Thread: BUG #2198: Now returns always same date and time during a session

The following bug has been logged online:

Bug reference:      2198
Logged by:          Jacques Gollion
Email address:      jgollion@sambatechnologies.com
PostgreSQL version: 8.1.2
Operating system:   Windows 2000
Description:        Now returns always same date and time during a session
Details:

The following functions returns the first time the right date and time but
when called at several date and time returns the date that was returned at
the first call. To get again the right date, it is necessary do disconnect
and reconnect.

===================================================
CREATE OR REPLACE FUNCTION getserverdate(szdatetime_p "varchar")
  RETURNS "varchar" AS
$BODY$
DECLARE  tNow_l timestamp ;
DECLARE  szdatetime_l VARCHAR(128);

begin
  szdatetime_l := '-1';
  tNow_l := Now();
  RAISE LOG '  tNow_l  = %', tNow_l;
  szdatetime_l := TO_CHAR(tNow_l,'YYYYMMDDHH24MISS');
   RAISE LOG '  szdatetime_l  = %', szdatetime_l;
 return szdatetime_l;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getserverdate(szdatetime_p "varchar") OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getserverdate(szdatetime_p "varchar") TO public;
GRANT EXECUTE ON FUNCTION getserverdate(szdatetime_p "varchar") TO
postgres;
GRANT EXECUTE ON FUNCTION getserverdate(szdatetime_p "varchar") TO client;
GRANT EXECUTE ON FUNCTION getserverdate(szdatetime_p "varchar") TO batch;

"Jacques Gollion" <jgollion@sambatechnologies.com> writes:
> The following functions returns the first time the right date and time but
> when called at several date and time returns the date that was returned at
> the first call.

Please read
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

> To get again the right date, it is necessary do disconnect and reconnect.

A fresh transaction is sufficient.

            regards, tom lane

On Mon, Jan 23, 2006 at 11:40:43AM +0000, Jacques Gollion wrote:
> The following functions returns the first time the right date and time but
> when called at several date and time returns the date that was returned at
> the first call.

See "Current Date/Time" in the documentation:

http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

"It is important to know that CURRENT_TIMESTAMP and related functions
return the start time of the current transaction; their values do
not change during the transaction.  This is considered a feature:
the intent is to allow a single transaction to have a consistent
notion of the 'current' time, so that multiple modifications within
the same transaction bear the same time stamp."

"There is also the function timeofday() which returns the wall-clock
time and advances during transactions."

> To get again the right date, it is necessary do disconnect and reconnect.

Do you have autocommit disabled?  I'd guess all of your function
calls are happening in the same transaction.  You shouldn't have
to reconnect; starting a new transaction should work.

--
Michael Fuhr