CODEWITHSUNDEEP
google-bigquery
ornachshon
ornachshon

Reputation: 105

Trying to convert epoch code field (string type) to timestamp

Trying to convert from epoch code to timestamp, in legacy SQL it's working fine and in standard SQL I'm getting an error. Field name custom_field_6 (string type field) represent epoch time and I want to convert it to timestamp (in standard SQL)

In legacy SQL I did the following: select custom_field_6 as custom_field_6, timestamp(custom_field_6) as custom_field_6_convert FROM [yellowhead-visionbi-rivery:yellowhead_prod.affise_conversions]

The query output

In standard sql:

select custom_field_6 as custom_field_6, cast(custom_field_6 as date) as custom_field_6_converted FROMyellowhead-visionbi-rivery.yellowhead_prod.affise_conversions``

The error I get: "invalid date"

Upvotes: 2

Views: 124

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

Below id for BigQuery Standard SQL 

#standardSQL
SELECT 
  custom_field_6, 
  TIMESTAMP_SECONDS(CAST(custom_field_6 AS INT64)) custom_field_6_convert
FROM `yellowhead-visionbi-rivery.yellowhead_prod.affise_conversions`

you can test it using dummy data as below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1540051185' custom_field_6 UNION ALL
  SELECT '1540252572' 
)
SELECT 
  custom_field_6, 
  TIMESTAMP_SECONDS(CAST(custom_field_6 AS INT64)) custom_field_6_convert
FROM `project.dataset.table`

with result 

Row custom_field_6  custom_field_6_convert   
1   1540051185      2018-10-20 15:59:45 UTC  
2   1540252572      2018-10-22 23:56:12 UTC

Upvotes: 1

Related Questions