How to convert a postgres timestamp to a string with the timezone as numeric offset?
<p>My query needs to format my <code>timestamp without time zone</code>columns into a format which in Java I can obtain like this:</p>

<pre><code>SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZZZZZ")
</code></pre>

<p>For example:
<code>2015-12-21T16:29:07.000-06:00</code> or <code>2016-04-18T10:10:09.000+0000</code></p>

<p>Now, I know the format wants a timezone, but it's OK to assume timezone = UTC.</p>

<p>What I tried:</p>

<pre><code>select to_char(updated_at, 'yyyy-MM-dd''T''HH:mm:ss.SSSZZZZZZ') from teams
2016-04-07'T'05:04:47.47SZZZZZZ

select to_char(updated_at::TIMESTAMPTZ, 'yyyy-MM-dd''T''HH:mm:ss.SSSZZZZZZ') from teams
2016-04-07'T'05:04:47.47SZZZZZZ
</code></pre>

<p>Also tried with postgres' 'tz' and 'TZ' it's just not capable to output a numeric timezone!</p>

<pre><code>select to_char(updated_at::TIMESTAMPTZ, 'yyyy-MM-dd''T''HH:mm:ss.SS TZ') from teams
2016-04-07'T'05:04:47.47 BST
</code></pre>

<p>I just can't get the numeric representation of the timezone, is this intentional? :(</p>