0

I would like to convert my spelled out weekday and month to timestamp format in Bigquery.

My dates are currently stored as Mon Jul 27 07:08:00 +0000 2015 and I want to convert them to 2015-07-27 07:08:00 UTC

I have read answer from How do I cast dd/mm/yyyy string into date in BigQuery? and tried to format it the following way 

SELECT TIMESTAMP(year + '-' + month + '-' + day) as output_timestamp
FROM (
  SELECT 
REGEXP_EXTRACT(created_at, '.*/([0-9]{4})$') as year, 
REGEXP_EXTRACT(created_at, '^([0-9]{2}).*') as day, 
REGEXP_EXTRACT(created_at, r'(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)') AS month  
)

[dataset.tablename] where collected_at = "2016-01-01 00:00:01" LIMIT 100

However, I get this error query: SELECT query which references non constant fields or uses aggregation functions or has one or more of WHERE, OMIT IF, GROUP BY, ORDER BY clauses must have FROM clause. (error code: invalidQuery)

How can I convert it to the format I want?

Community
  • 1
  • 1
Avi
  • 410
  • 3
  • 14

1 Answers1

1

Using Standard SQL, you have PARSE_TIMESTAMP function with lots of format elements, so you can convert your string into TIMESTAMP, and if later you need to build custom string out of it, you can use FORMAT_TIMESTAMP. In your example the following format string would work:

SELECT PARSE_TIMESTAMP("%a %b %d %T +0000 %E4Y", t) FROM
(SELECT 'Mon Jul 27 07:08:00 +0000 2015' t)

Formats used:

%a - Abbreviated weekday
%b - Abbrevialted month
%d - Time in HH:MM:SS
%E4Y - 4 digits year
Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
  • Thank you so much. For those who might try this you also need to uncheck SQL Version Use Legacy SQL so that this code work. Else you will get 'bigquery Error: 2.24 - 2.84'. – Avi Sep 23 '16 at 00:22