CODEWITHSUNDEEP
google-bigquery
Backlin
Backlin

Reputation: 14872

Timestamp to string with custom format

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

Upvotes: 5

Views: 18837

Answers (3)

Alex Spangher
Alex Spangher

Reputation: 997

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

Upvotes: 11

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

check STRFTIME_UTC_USEC

SELECT STRFTIME_UTC_USEC(CURRENT_TIMESTAMP(), '%d/%m/%Y %H:%M:%S')

or with your example 

SELECT STRFTIME_UTC_USEC(TIMESTAMP(132456789101112), '%d/%m/%Y %H:%M:%S')

Upvotes: 7

N.N.
N.N.

Reputation: 3172

Its not a straightforward function, but it does the job...

SELECT
  t,
  CONCAT( LPAD(STRING(DAY(t)),2,"0"),"/",LPAD(STRING(MONTH(t)),2,"0"),"/",STRING(YEAR(t))," ",TIME(t)) s
FROM (
  SELECT
    TIMESTAMP(132456789101112) t)

Upvotes: 1

Related Questions