Search Postgresql Archives

Returning timestamp with timezone at specified timezone irrespective of client timezone

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

I've attempted to obtain help with this problem from several other places, but numerous individuals recommended I ask this mailing list.

What I need is for the ability to return a timestamp with timezone, using the UTC offset that corresponds to a column-defined timezone, irrespective of the client/session configured timezone.

I have three columns in a table:
Timezone: 'US/Eastern'
Date: 2020-10-31
Time: 08:00

The output I'm able to find includes these possibilities:
'2020-10-31 08:00:00'
'2020-10-31 12:00:00+00'

Whereas what I actually need is:
'2020-10-31 08:00:00-05'

Using the postgresql session-level timezone configuration won't work because I need multiple timezones to be handled in a single set.

Example code follows. I'm not using to_char in the examples as I likely would in the production code, but I haven't found any way that it could be helpful here regardless.

---------------------------------------------------

SET TIME ZONE 'UTC';

CREATE TABLE loc
(
    id serial not null,
timezone text not null,
    loc_date date NOT NULL,
    loc_time text NOT NULL,
    CONSTRAINT loc_pkey PRIMARY KEY (id),
    CONSTRAINT loc_loc_time_check CHECK (loc_time ~ '(^(2[0-3]|[01][0-9]|[0-9]):?([0-5][0-9]):?([0-5][0-9])?$)|(^(1[012]|0[1-9]|[1-9]):[0-5][0-9] [AaPp][Mm]$)'::text)
)
;

INSERT INTO loc (timezone, loc_date, loc_time) VALUES
('US/Eastern', '2020-10-31', '08:00'),
('US/Eastern', '2020-11-03', '08:00'),
('US/Central', '2020-10-31', '08:00'),
('US/Central', '2020-11-03', '08:00');

SELECT *
, timezone(l.timezone, l.loc_date + l.loc_time::time without time zone) tswtz
, (l.loc_date + l.loc_time::time without time zone) tswotz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

---------------------------------------------------

id |  timezone  |  loc_date  | loc_time |         tswtz          |       tswotz
----+------------+------------+----------+------------------------+---------------------
  7 | US/Central | 2020-10-31 | 08:00    | 2020-10-31 13:00:00+00 | 2020-10-31 08:00:00
  8 | US/Central | 2020-11-03 | 08:00    | 2020-11-03 14:00:00+00 | 2020-11-03 08:00:00
  5 | US/Eastern | 2020-10-31 | 08:00    | 2020-10-31 12:00:00+00 | 2020-10-31 08:00:00
  6 | US/Eastern | 2020-11-03 | 08:00    | 2020-11-03 13:00:00+00 | 2020-11-03 08:00:00
(4 rows)

What I actually need is, in example id=7, '2020-10-31 08:00:00-05'.

Is this even possible? Several people have proposed that I write a custom function to do this on a per-row basis, which... I suppose I can do... I'm just blown away that this isn't something that just works "out of the box".


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux