Htykuut

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"

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