Thread: at time zone question in pl/pgSQL

at time zone question in pl/pgSQL

From

"Heather Grace"

Date:

14 February 2002, 15:14:20

Using postgresql version 7.1

I'm trying to write a simple pl/pgsql function that will return the current time for the given time zone ( i have to deal multiple time zones) . But I don't seem to be using the appropriate syntax. I keep getting an error on the 'at time zone...'

DECLARE

new_time TIMESTAMP;

BEGIN

SELECT INTO new_time now() at time zone 'PST'; /*just hard coding the time zone for now */

return new_time;

END;

The following select statement works from an SQL query:

SELECT now() at time zone 'PST'

and returns the appropriate time.

I have also tried using the following statements:

Set TimeZone to PST;

return now();

but that didn't give me the time for PST, only the default time zone, EST.

thanks in advance,

--heather grace

Re: at time zone question in pl/pgSQL

From

"Gregory Wood"

Date:

18 February 2002, 19:23:54

> I'm trying to write a simple pl/pgsql function that will return the
current time for the given time zone ( i have to deal multiple time zones) .
But I don't seem to be using the appropriate syntax.   I keep getting an
error on the 'at time zone...'

> DECLARE
>   new_time TIMESTAMP;
> BEGIN
>    SELECT INTO new_time now() at time zone 'PST';   /*just hard coding the
time zone for now */
>   return new_time;
> END;

Assuming that this is a direct cut and paste, the error would appear to be
that the single quotes around the timezone are not escaped. Try changing
that line to:

   SELECT INTO new_time now() at time zone ''PST'';   /*just hard coding the
time zone for now */

If that's not it, nevermind :)

Greg