Обсуждение: Changing displayed time zone in RAISE NOTICE output?
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.
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
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.
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
č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=#
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
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
č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