Forum OpenACS Q&A: sysdate() problem with OpenAcs 4

Posted by Claudio Pasolini on
I recently installed the OpenAcs 4 tarball on a machine already
running OpenAcs 3.2.5 (thanks Jonathan!) and so I did nothing about
Aolserver and PostgreSQL. The installation was good and I can
connect and navigate into the site. I tried to exec some working
scripts until I got an (apparently) incredible error: PostgreSQL
doesn't recognize the sysdate() function:

openacs-4=# select sysdate();
ERROR:  Function 'sysdate()' does not exist
    Unable to identify a function that satisfies the given
argument types
    You may need to add explicit typecasts

Note that if I open psql with the DB used by OpenAcs 3.2.5 all is OK.

Any idea?

TIA Claudio

Posted by Jonathan Ellis on
The pg equivalent of sysdate is now. The alias for 3.2.x is
-- sysdate hack to make things look somewhat alike
create function sysdate() returns datetime as '
        return ''now'';
end;' language 'plpgsql';
Posted by Don Baccus on
Even better is to use CURRENT_TIMESTAMP, which is more or less the preferred way to get the current time these days in PG.
Posted by Jonathan Ellis on
but a whole lot more typing. :)
Posted by Bob OConnor on

Ok this works in psql:

mydata=# select now() from dual;
 2001-10-24 14:51:05-04
(1 row)

But this does not:

mydata=# select current_timestamp() from dual;
ERROR:  parser: parse error at or near ")"

I guess I'll stick with the here and NOW() !


Posted by Vinod Kurup on
it should be:
openacs4=# select current_timestamp;
 2001-10-24 16:00:25-04
(1 row)
Posted by Don Baccus on
I think current_timestamp may be SQL92 compliant and that this is why it's preferred, but I'm not 100% certain ... now() is most definitely not portable.
Posted by Roberto Mello on

The reason why sysdate() works in your 3.2.5 database, is because that function is defined in the 3.2.5 datamodel, but it probably isn't defined in the 4.2 datamodel. The functions of one database are not transplanted to another.

As others have noted, it's better to use the standard CURRENT_TIMESTAMP.