0

I have a PostgreSQL DB and have a script which calculates dates from an old Sybase DB. How can I do the same thing is PostgreSQL ? 

isql 

DBCOMMAND="eval isql -d $DATABASE -U user -P passwd "

$DBCOMMAND << MSG > $LOG_MEM_EXCEP

DECLARE @PREVINTDATETIME DATETIME 

select @PREVINTDATETIME=(DATEADD(hh, -24, GETDATE()))

DECLARE @CURDATE DATETIME

select CURDATE=GETDATE()

select XTIME, MESSAGE from EXCEPTION_ALERTS where (XTIME between @PREVINTDATETIME AND @CURDATE)

exit MSG
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sharon
  • 401
  • 1
  • 4
  • 5

1 Answers1

1

Basically it burns down to a simple SQL statement:

SELECT xtime, message
FROM   exception_alerts
WHERE  xtime BETWEEN now() - interval '1d' AND now();

.. returning two columns of all rows in table exception_alerts from the last 24 hours.

Did you want to make a sql or plpgsql function out of it? Or call it from the shell using the command line interface psql? What is the exact form you would expect in return? Including column names?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I'm calling it from a shell script. We use to run this on a Sybase DB which is now a PostgreSQL DB. So I'am trying to convert to psql. – Sharon Nov 21 '11 at 01:00
  • I am using DBCOMMAND="eval psql -d $DATABASE " instead. and need to create some variables that a DATETIME type variables - so it will calculate -24 hours - I hope that makes more sense. I want to only display the xtime and message colums, if the xtime colume is between the 2 calculated times – Sharon Nov 21 '11 at 01:03
  • I did actually test your simple SQL statement and it worked a treat so I will use that - Thanks so much for your help ! – Sharon Nov 21 '11 at 01:46