Обсуждение: now() + '4d' AT TIME ZONE issue

Hi all,

   I'm trying to select an offset timestamp at a given time zone, but I
can't seem to get the syntax right.

What I am *trying* to do, which doesn't work:

SELECT
    now() AT TIME ZONE 'America/Toronto',
    now() + '4d' AS future AT TIME ZONE 'America/Toronto';

Which generates the error:
ERROR:  syntax error at or near "AT"
LINE 1: ...ME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZO...

I've tried using an embedded SELECT and CASTing it as a TIMESTAMP with
no luck.

SELECT
    now() AT TIME ZONE 'America/Toronto',
    CAST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZONE
'America/Toronto';
ERROR:  syntax error at or near "AT"
LINE 1: ...ST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZO...

When I remove the 'AT TIME ZONE' from the offset now in either case the
SELECT works.

Someone mind beating me with a clue stick? Thanks!

Madi

Try moving your "as future"
SELECT
    now() AT TIME ZONE 'America/Toronto',
    CAST ((SELECT now() + '4d') AS TIMESTAMP) AT TIME ZONE 'America/Toronto' as future;

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Madison Kelly
> Sent: Wednesday, July 08, 2009 1:33 PM
> To: postgres list
> Subject: [GENERAL] now() + '4d' AT TIME ZONE issue
>
> Hi all,
>
>    I'm trying to select an offset timestamp at a given time zone, but I
> can't seem to get the syntax right.
>
> What I am *trying* to do, which doesn't work:
>
> SELECT
>     now() AT TIME ZONE 'America/Toronto',
>     now() + '4d' AS future AT TIME ZONE 'America/Toronto';
>
> Which generates the error:
> ERROR:  syntax error at or near "AT"
> LINE 1: ...ME ZONE 'America/Toronto', now() + '4d' AS future AT TIME
> ZO...
>
> I've tried using an embedded SELECT and CASTing it as a TIMESTAMP with
> no luck.
>
> SELECT
>     now() AT TIME ZONE 'America/Toronto',
>     CAST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZONE
> 'America/Toronto';
> ERROR:  syntax error at or near "AT"
> LINE 1: ...ST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME
> ZO...
>
> When I remove the 'AT TIME ZONE' from the offset now in either case the
> SELECT works.
>
> Someone mind beating me with a clue stick? Thanks!
>
> Madi
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Madison Kelly <linux@alteeve.com> writes:
> SELECT
>     now() AT TIME ZONE 'America/Toronto',
>     now() + '4d' AS future AT TIME ZONE 'America/Toronto';

You've got "AS future" in the wrong place.

            regards, tom lane

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>> SELECT
>>     now() AT TIME ZONE 'America/Toronto',
>>     now() + '4d' AS future AT TIME ZONE 'America/Toronto';
>
> You've got "AS future" in the wrong place.
>
>             regards, tom lane
>

Thank you both, Chris and Tom. That was indeed my oops.

Madi