CODEWITHSUNDEEP
sqlgoogle-bigquery
Arindam
Arindam

Reputation: 13

BigQuery convert String to Date

In my dataset, one column called timestamp was created with datatype as String.

It contains values like:

2018-05-30T12:56:27:487+0200

I want to construct a query where I can fetch all column from the dataset table based on the date in 'YYYY-MM-DD' format.

I want to use it in where clause with DATE Range between.

Can you guide?

Thank you.

Upvotes: 1

Views: 2938

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

If you want the date in the same timezone represented, then the simplest method is to use string operations and convert to a date:

select PARSE_DATE('%Y-%m-%d', SUBSTR('2018-05-30T12:56:27:487+0200', 1, 10))

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

convert String to Date

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '2018-05-30T12:56:27.487+0200' ts UNION ALL
  SELECT '2018-05-30T01:56:27.487+0200' 
)
SELECT ts AS ts_as_string, 
  PARSE_TIMESTAMP('%FT%H:%M:%E3S%z', ts) ts_as_timestamp,
  DATE(PARSE_TIMESTAMP('%FT%H:%M:%E3S%z', ts)) ts_as_date
FROM `project.dataset.table`

with result 

ts_as_string                    ts_as_timestamp                 ts_as_date   
2018-05-30T12:56:27.487+0200    2018-05-30 10:56:27.487 UTC     2018-05-30   
2018-05-30T01:56:27.487+0200    2018-05-29 23:56:27.487 UTC     2018-05-29

As you can see - first i am parsing timestamp out of the string - this is important part - this is where you can take timezone into account (see difference in dates in the result 2018-05-29 vs. 2018-05-29). Then you can get Date out of TIMESTAMP

I want to use it in where clause with DATE Range between.

So, now you can use below in your WHERE clause 

WHERE DATE(PARSE_TIMESTAMP('%FT%H:%M:%E3S%z', ts)) BETWEEN date1 AND date2

Update

You can use below to avoid dealing with "wrong" format 

PARSE_DATE('%F',  SUBSTR(ts, 1, 10))

In case if you need to account for timezone - you can use below (which fix : to . before applying PARSE_TIMESTAMP) 

DATE(PARSE_TIMESTAMP('%FT%H:%M:%E3S%z', FORMAT('%s.%s', SUBSTR(ts, 1, 19), SUBSTR(ts, 21, 8))))

Upvotes: 3

Related Questions