Timestamp to string with custom format
<p>I am to move data from BigQuery to an Oracle database and am trying to find the best way to deal with timestamps. The Oracle DB can only import csv files with dates in little endian format (dd/mm/yyyy hh:mi:ss) but by default BigQuery only supports big endian (yyyy-mm-dd hh:mi:ss).</p>

<pre class="lang-sql prettyprint-override"><code>SELECT
  t,
  STRING(t) s
FROM
  (SELECT TIMESTAMP(132456789101112) t)
</code></pre>

<pre class="lang-none prettyprint-override"><code>Row   t                         s
1     1974-03-14 01:33:09 UTC   1974-03-14 01:33:09.101112
</code></pre>

<p>I could of course extract the different components of the timestamp and paste them together manually (see below) or write some clever UDF, but I'd be surprised if there isn't any way to do this with BigQuery's standard functionality. It seems like such a common thing to do that I've in fact hesitated to ask for some time.</p>

<pre class="lang-sql prettyprint-override"><code>SELECT
  t,
  CONCAT(
    RIGHT(CONCAT("0", STRING(DAY(t))), 2), "/",
    RIGHT(CONCAT("0", STRING(MONTH(t))), 2), "/",
    RIGHT(CONCAT("000", STRING(YEAR(t))), 4), " ",
    TIME(t)
  ) s
FROM
  (SELECT TIMESTAMP(132456789101112) t)
</code></pre>

<pre class="lang-none prettyprint-override"><code>Row   t                         s    
1     1974-03-14 01:33:09 UTC   14/03/1974 01:33:09
</code></pre>

<p>Is there anything like <code>FORMAT(t, "dd/mm/yyyy hh:mi:ss")</code> or a way to do it with regexps? (Without having to concat several <code>REGEXP_EXTRACT</code>.)</p>