Timestamp to string with custom format

Question

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).

SELECT
  t,
  STRING(t) s
FROM
  (SELECT TIMESTAMP(132456789101112) t)

Row   t                         s
1     1974-03-14 01:33:09 UTC   1974-03-14 01:33:09.101112

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.

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)

Row   t                         s    
1     1974-03-14 01:33:09 UTC   14/03/1974 01:33:09

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

Answer 1

For those of you using Bigquery's standard SQL, the right way to do this is with

FORMAT_TIMESTAMP or FORMAT_DATE depending on your input.

ex:

SELECT FORMAT_TIMESTAMP('%Y/%m/%d', TIMESTAMP('2017-11-01')) will return 2017/11/01