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

Collapse
1: sysdate() problem with OpenAcs 4
Posted by Claudio Pasolini on 10/24/01 05:29 PM
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

Collapse
2: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Jonathan Ellis on 10/24/01 06:04 PM
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 '
begin
        return ''now'';
end;' language 'plpgsql';
Collapse
3: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Don Baccus on 10/24/01 07:33 PM
Even better is to use CURRENT_TIMESTAMP, which is more or less the preferred way to get the current time these days in PG.
Collapse
4: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Jonathan Ellis on 10/24/01 09:11 PM
but a whole lot more typing. :)
Collapse
5: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Bob OConnor on 10/24/01 09:39 PM

Ok this works in psql: 

mydata=# select now() from dual;
          now           
------------------------
 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() !

-Bob

Collapse
6: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Vinod Kurup on 10/24/01 09:56 PM
it should be: 
openacs4=# select current_timestamp;
       timestamp        
------------------------
 2001-10-24 16:00:25-04
(1 row)
Collapse
7: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Don Baccus on 10/25/01 02:20 AM
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.
Collapse
8: Response to sysdate() problem with OpenAcs 4 (response to 1)
Posted by Roberto Mello on 10/26/01 12:26 AM
Claudio,

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.