Thread: Changing displayed time zone in RAISE NOTICE output?

Changing displayed time zone in RAISE NOTICE output?

From

Ron

Date:

12 January 2023, 00:00:38

How do I get clock_timestamp() to display the time in a different time zone?

This is America/Chicago, but I'd like to display it in a different TZ.

psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1))
postgres=# DO $$
postgres$# BEGIN
postgres$# RAISE NOTICE '%', clock_timestamp();
postgres$# END$$;
NOTICE:  2023-01-11 14:52:34.408225-06
DO

-- 
Born in Arizona, moved to Babylonia.

Re: Changing displayed time zone in RAISE NOTICE output?

From

Adrian Klaver

Date:

12 January 2023, 00:06:36

On 1/11/23 13:00, Ron wrote:
> How do I get clock_timestamp() to display the time in a different time 
> zone?
> 
> This is America/Chicago, but I'd like to display it in a different TZ.
> 
> psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1))
> postgres=# DO $$
> postgres$# BEGIN
> postgres$# RAISE NOTICE '%', clock_timestamp();
> postgres$# END$$;
> NOTICE:  2023-01-11 14:52:34.408225-06
> DO
> 

test(5432)=# select clock_timestamp();
         clock_timestamp
--------------------------------
  01/11/2023 13:05:54.646178 PST
(1 row)

test(5432)=# select clock_timestamp() at time zone 'UTC';
           timezone
----------------------------
  01/11/2023 21:06:04.742478


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Changing displayed time zone in RAISE NOTICE output?

From

Ron

Date:

12 January 2023, 08:25:55

On 1/11/23 15:06, Adrian Klaver wrote:
> On 1/11/23 13:00, Ron wrote:
>> How do I get clock_timestamp() to display the time in a different time zone?
>>
>> This is America/Chicago, but I'd like to display it in a different TZ.
>>
>> psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1))
>> postgres=# DO $$
>> postgres$# BEGIN
>> postgres$# RAISE NOTICE '%', clock_timestamp();
>> postgres$# END$$;
>> NOTICE:  2023-01-11 14:52:34.408225-06
>> DO
>>
>
> test(5432)=# select clock_timestamp();
>         clock_timestamp
> --------------------------------
>  01/11/2023 13:05:54.646178 PST
> (1 row)
>
> test(5432)=# select clock_timestamp() at time zone 'UTC';
>           timezone
> ----------------------------
>  01/11/2023 21:06:04.742478

Hmm.  I'd have sworn this didn't work when I tried it:

postgres=#
postgres=# DO $$
BEGIN
RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
END$$;
NOTICE:  2023-01-12 05:22:40.517299

But it does work, so all's well that ends well.

-- 
Born in Arizona, moved to Babylonia.

Re: Changing displayed time zone in RAISE NOTICE output?

From

Adrian Klaver

Date:

12 January 2023, 18:38:54

On 1/11/23 21:25, Ron wrote:
> On 1/11/23 15:06, Adrian Klaver wrote:

> 
> Hmm.  I'd have sworn this didn't work when I tried it:

Did you do?:

DO $$
BEGIN
RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
END$$;
ERROR:  syntax error at or near "timezone"
LINE 3: RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';


> 
> postgres=#
> postgres=# DO $$
> BEGIN
> RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
> END$$;
> NOTICE:  2023-01-12 05:22:40.517299
> 
> But it does work, so all's well that ends well.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Changing displayed time zone in RAISE NOTICE output?

From

Pavel Stehule

Date:

12 January 2023, 19:22:33

čt 12. 1. 2023 v 16:39 odesílatel Adrian Klaver <adrian.klaver@aklaver.com> napsal:

On 1/11/23 21:25, Ron wrote:
> On 1/11/23 15:06, Adrian Klaver wrote:

>
> Hmm. I'd have sworn this didn't work when I tried it:

Did you do?:

DO $$
BEGIN
RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
END$$;
ERROR: syntax error at or near "timezone"
LINE 3: RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';

there should be space >>AT TIME ZONE<<

(2023-01-12 17:21:10) postgres=# DO $$
BEGIN
RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
END$$;
NOTICE: 2023-01-12 16:21:14.063256
DO
(2023-01-12 17:21:14) postgres=#

>
> postgres=#
> postgres=# DO $$
> BEGIN
> RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
> END$$;
> NOTICE: 2023-01-12 05:22:40.517299
>
> But it does work, so all's well that ends well.
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Changing displayed time zone in RAISE NOTICE output?

From

Adrian Klaver

Date:

12 January 2023, 19:27:46

On 1/12/23 08:22, Pavel Stehule wrote:
> 
> 
> čt 12. 1. 2023 v 16:39 odesílatel Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> napsal:
> 
>     On 1/11/23 21:25, Ron wrote:
>      > On 1/11/23 15:06, Adrian Klaver wrote:
> 
>      >
>      > Hmm.  I'd have sworn this didn't work when I tried it:
> 
>     Did you do?:
> 
>     DO $$
>     BEGIN
>     RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
>     END$$;
>     ERROR:  syntax error at or near "timezone"
>     LINE 3: RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
> 
> 
> there should be space >>AT TIME ZONE<<
> 
> (2023-01-12 17:21:10) postgres=# DO $$
> BEGIN
> RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
> END$$;
> NOTICE:  2023-01-12 16:21:14.063256
> DO
> (2023-01-12 17:21:14) postgres=#

Yeah I know, I was responding to Ron saying his previous attempt had 
failed. Using 'timezone' instead of 'time zone' is a mistake I have made 
often enough that I threw it out there as a possible cause for the failure.

> 
> 
>      >
>      > postgres=#
>      > postgres=# DO $$
>      > BEGIN
>      > RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
>      > END$$;
>      > NOTICE:  2023-01-12 05:22:40.517299
>      >
>      > But it does work, so all's well that ends well.
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Changing displayed time zone in RAISE NOTICE output?

From

Pavel Stehule

Date:

12 January 2023, 19:29:55

čt 12. 1. 2023 v 17:27 odesílatel Adrian Klaver <adrian.klaver@aklaver.com> napsal:

On 1/12/23 08:22, Pavel Stehule wrote:
>
>
> čt 12. 1. 2023 v 16:39 odesílatel Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> napsal:
>
> On 1/11/23 21:25, Ron wrote:
> > On 1/11/23 15:06, Adrian Klaver wrote:
>
> >
> > Hmm. I'd have sworn this didn't work when I tried it:
>
> Did you do?:
>
> DO $$
> BEGIN
> RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
> END$$;
> ERROR: syntax error at or near "timezone"
> LINE 3: RAISE NOTICE '%', clock_timestamp() at timezone 'UTC';
>
>
> there should be space >>AT TIME ZONE<<
>
> (2023-01-12 17:21:10) postgres=# DO $$
> BEGIN
> RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
> END$$;
> NOTICE: 2023-01-12 16:21:14.063256
> DO
> (2023-01-12 17:21:14) postgres=#

Yeah I know, I was responding to Ron saying his previous attempt had
failed. Using 'timezone' instead of 'time zone' is a mistake I have made
often enough that I threw it out there as a possible cause for the failure.

:-)

>
>
> >
> > postgres=#
> > postgres=# DO $$
> > BEGIN
> > RAISE NOTICE '%', clock_timestamp() at time zone 'UTC';
> > END$$;
> > NOTICE: 2023-01-12 05:22:40.517299
> >
> > But it does work, so all's well that ends well.
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com