Converting TIMESTAMPTZs to DATEs in a specified time zone

[Kodiologist]

Suppose I have this data:

create table T(x timestamptz);
insert into T select * from range(
    timestamptz '2015-03-07T00:00:00Z',
    timestamptz '2015-03-09T00:00:00Z',
    interval '1 hour');

How can I produce a new column of type DATE where each value is the date of the corresponding x value in UTC-6 (i.e., what the Olson database calls Etc/GMT+6)? Thus timestamptz '2015-03-07T00:00:00Z' should become date '2015-03-06', and timestamptz '2015-03-07T07:00:00Z' should be become date '2015-03-07'.

[soerenwolfers]

SELECT 
    x,
    timezone('Etc/GMT+6', x) AS localtime,
    localtime::DATE AS localdate,
FROM T 
ORDER BY x 
LIMIT 10;

┌──────────────────────────┬─────────────────────┬────────────┐
│            x             │      localtime      │ localdate  │
│ timestamp with time zone │      timestamp      │    date    │
├──────────────────────────┼─────────────────────┼────────────┤
│ 2015-03-07 00:00:00+00   │ 2015-03-06 18:00:00 │ 2015-03-06 │
│ 2015-03-07 01:00:00+00   │ 2015-03-06 19:00:00 │ 2015-03-06 │
│ 2015-03-07 02:00:00+00   │ 2015-03-06 20:00:00 │ 2015-03-06 │
│ 2015-03-07 03:00:00+00   │ 2015-03-06 21:00:00 │ 2015-03-06 │
│ 2015-03-07 04:00:00+00   │ 2015-03-06 22:00:00 │ 2015-03-06 │
│ 2015-03-07 05:00:00+00   │ 2015-03-06 23:00:00 │ 2015-03-06 │
│ 2015-03-07 06:00:00+00   │ 2015-03-07 00:00:00 │ 2015-03-07 │
│ 2015-03-07 07:00:00+00   │ 2015-03-07 01:00:00 │ 2015-03-07 │
│ 2015-03-07 08:00:00+00   │ 2015-03-07 02:00:00 │ 2015-03-07 │
│ 2015-03-07 09:00:00+00   │ 2015-03-07 03:00:00 │ 2015-03-07 │
├──────────────────────────┴─────────────────────┴────────────┤
│ 10 rows                                           3 columns │
└─────────────────────────────────────────────────────────────┘

[soerenwolfers]

Legacy syntax for the timezone function: x AT TIME ZONE 'Etc/GMT+6'

[Kodiologist]

Perfect, thanks. I think what I was struggling with was the fact that the timezone function converts to a naive object, which is not a problem if you immediately take the part that you want (like a date) out of it.

[soerenwolfers]

FWIW, there are also (worse, IMO) alternative solutions via SET TimeZone = 'Etc/GMT+6'; and then SELECT date_trunc('day', x::TIMESTAMP) or SELECT date_trunc('day', x)::DATE

[soerenwolfers]

Personally, I prefer all time / date manipulations be explicit. One step towards that was taken in #11570, more steps were proposed in #12040, and ultimately I think users that want to avoid footguns should be able to completely disable any use of the TimeZone setting.

[Kodiologist]

I get ya. Any way that code is implicitly locale-dependent is dangerous.