title: PostgreSQL UTC to CET/CEST
body: <p>I have some big trouble with timezones and Benjamin Franklin.</p>
  
  <p>I have a table with an UTC timestamp field, no timezone is stored.</p>
  
  <p>My goal is to group the rows of this table by day of week or by slices like n hours.</p>
  
  <p>This id how I proceeded until now :</p>
  
  <pre><code>-- CET  =&gt; UTC/GMT + 1 =&gt; Winter
  -- CEST =&gt; UTC/GMT + 2 =&gt; Summer
  --
  -- Hour changes 2014 : march 30th, 2h CET =&gt; 3h CEST
  --
  --
  --      Output for "d"             Wanted output for "d"
  -- "2014-03-28 23:00:00+00"   "2014-03-28 23:00:00+00" (23h UTC because CET)
  -- "2014-03-29 23:00:00+00"   "2014-03-29 23:00:00+00" (23h UTC because CET)
  -- "2014-03-30 23:00:00+00"   "2014-03-30 22:00:00+00" (22h UTC because CEST : fail because start timestamp is CET)
  
  SELECT dates.d AS d, archives_d, range_begin_date, sequentialid
  
    FROM generate_series('2014-03-28T23:00:00+00:00'::timestamp, -- 23h UTC because CET -- 2014-03-28T22:00:00+00:00
                         '2014-03-31T21:59:59+00:00'::timestamp, -- 22h UTC because CEST -- 2014-03-31T21:59:59+00:00
                         '86400 seconds') AS dates(d)
  
    LEFT JOIN (
       -- Select archive date floored by a step (here 86400 seconds -&gt; 24 hours)
       SELECT *, (to_timestamp(((floor(extract(epoch from (archives.range_begin_date - '2014-03-28T23:00:00+00:00'::timestamp)) / 86400)) * 86400) + extract(epoch from '2014-03-28T23:00:00+00:00'::timestamp))) AS archives_d
       FROM archives
    ) AS archives
      ON dates.d = archives.archives_d
  
   ORDER BY dates.d
  </code></pre>
  
  <p>Do you have any idea of how get the wanted output (at least for the generate series).</p>
  
  <p>Note that my step for the generate series is not fixed at one day but an arbitrary interval.</p>
  
  <p>Thanks</p>