title: PostgreSQL grouping by day with timezone and DST
body: <p>I'm working on an existing database which date+time values are stored in <code>BIGINT</code> column (<em>milliseconds since EPOCH</em>). For each entry I can get the corresponding time zone from another table. But to make things easier to understand I will explain my problem by simulating the problem.</p>
  <p>The timestamp <code>1609534800000</code> is equal to <code>2021-01-01 21:00:00</code> at GMT (00-00)</p>
  <p>Now if I run the following queries (with DBeaver)</p>
  <pre class="lang-sql prettyprint-override"><code>
  set time zone 'America/Asuncion';  -- (UTC-3)
  
  select 
       to_timestamp(1609534800000 / 1000) as &quot;1&quot;
     , to_timestamp(1609534800000 / 1000) at TIME zone 'America/New_York' as &quot;2&quot;
     , date_trunc('day', (to_timestamp(1609534800000 / 1000) at TIME zone 'America/New_York')) as &quot;3&quot;
     , to_timestamp(1609534800000 / 1000) at TIME zone 'Pacific/Wake' as &quot;4&quot;
     , date_trunc('day', (to_timestamp(1609534800000 / 1000) at TIME zone 'Pacific/Wake')) as &quot;5&quot;
     , date_part('epoch', (to_timestamp(1609534800000 / 1000) at TIME zone 'Pacific/Wake')) as &quot;6&quot;
     , date_part('epoch', (date_trunc('day', (to_timestamp(1609534800000 / 1000) at TIME zone 'Pacific/Wake') ) ) ) as &quot;7&quot;
  </code></pre>
  <p>I'm getting</p>
  <pre><code>1                  |2                  |3                  |4                  |5                  |6         |7         |
  -------------------|-------------------|-------------------|-------------------|-------------------|----------|----------|
  2021-01-01 16:00:00|2021-01-01 16:00:00|2021-01-01 00:00:00|2021-01-02 09:00:00|2021-01-02 00:00:00|1609578000|1609545600|
  </code></pre>
  <ol>
  <li><strong>I don't understand the result at all</strong>. According to the documentation, the function <code>to_timestamp</code> is supposed to return a <code>timestamp with time zone</code> ? In this case the time zone applied should be the one in my session <code>America/Asuncion</code> (UTC-3). If at GMT the time is <code>2021-01-01 21:00:00</code>, I should get <code>2021-01-01 18:00:00</code>. Because (<code>21:00 - 3h = 18:00</code>). Why 16h ?</li>
  <li>From my understanding this result is OK as <code>2021-01-01 21:00:00</code> at UTC -5h for timezone <code>America/New_York</code> is equal to <code>2021-01-01 16:00</code>.</li>
  <li>Here I'm asking the same thing as the #2 but I want to discard the time of the day. So <code>2021-01-01 16:00:00</code> is <code>2021-01-01 00:00:00</code>. The result is OK.</li>
  <li>This result is still OK as <code>2021-01-01 21:00:00</code> at UTC + 12h for timezone <code>Pacific/Wake</code> is equal to <code>2021-01-02 09:00:00</code>.
  5 I'm asking the same thing as the #4 but I want to discard the time of the day. So <code>2021-01-02 09:00:00</code> is <code>2021-01-02 00:00:00</code>. The result is OK.</li>
  <li>I want to extract the unix EPOCH time in seconds of this timestamp. If I well understand, the timestamp pass to the <code>date_part</code> function is now a <code>timestamp without time zone</code>. Now if I use an <a href="https://www.epochconverter.com/" rel="nofollow noreferrer">online converter</a> to convert the resulting value <code>1609578000</code> to GMT time then I'm getting <code>2021-01-02 9:00:00</code>. Which is OK for me.</li>
  <li>This is the same operation as the #6 but I want the unix epoch from the beginning of the day of that local time. The resulting value <code>1609545600</code> correspond to the GMT time <code>2021-01-02 00:00:00</code>. Which <strong>is NOT correct</strong> as I should get <code>2021-01-02 12:00:00</code> as 'Pacific/Wake' is 12h past GMT.</li>
  </ol>
  <p>(<strong>UPDATED</strong>)
  Also why <code>Montreal</code> locale time is not correct here ? I'm supposed to have <code>2021-01-01 00:00:00-05</code></p>
  <pre class="lang-sql prettyprint-override"><code>select ((to_timestamp(1609477200000 /1000) at time zone 'America/Asuncion') at time zone 'America/Asuncion') as asuncion 
         , ((to_timestamp(1609477200000 /1000) at time zone 'America/Montreal') at time zone 'America/Montreal') as montreal
  
          asuncion        |        montreal
  ------------------------+------------------------
   2021-01-01 02:00:00-03 | 2021-01-01 02:00:00-03
  </code></pre>
  <p>How could I get</p>
  <pre><code>        asuncion        |        montreal
  ------------------------+------------------------
   2021-01-01 02:00:00-03 | 2021-01-01 00:00:00-05
  </code></pre>
  <p>Is there a way to see the time WITHOUT the configured session timeszone ?</p>
  <p><em>PS : My Windows OS timezone is set at <code>America/New_York</code> and I'm using PostgreSQL 10.</em></p>
  <p>Best regards,</p>