BigQuery TIMESTAMP TO DATETIME

user_1174302 0
Viewed 14463

Conversion rules in Google say that TIMESTAMPS can become DATETIME.

The following code works to turn a TIMESTAMP to a DATE. 

SELECT CAST( DATE(CURRENT_TIMESTAMP()) AS DATE)

We can get a DATETIME using this in Standard SQL but we lose the time in the process:

SELECT  CAST ( CAST( DATE(CURRENT_TIMESTAMP()) AS DATE) AS DATETIME )

How do we get a TIMESTAMP to become a DATETIME without losing the time (i.e. something like this)? 

SELECT CAST(   CURRENT_TIMESTAMP() AS DATETIME )

How do we do this in Legacy SQL and Standard SQL on BigQuery?

NOTE: We just discovered our example CAST works on Standard SQL (it appears our query system was using Legacy SQL).

google-bigquery
1 Answers

You can simply use CAST(... as DATETIME) as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT CURRENT_TIMESTAMP() ts_as_timestamp
)
SELECT ts_as_timestamp, CAST(ts_as_timestamp AS DATETIME) ts_as_datetime
FROM `project.dataset.table`

with result 

Row ts_as_timestamp                 ts_as_datetime   
1   2019-06-13 19:22:42.839108 UTC  2019-06-13T19:22:42.839108

For Legacy SQL 

#legacySQL
SELECT ts_as_timestamp, CAST(STRING(ts_as_timestamp) AS DATETIME) ts_as_datetime
FROM (SELECT CURRENT_TIMESTAMP() ts_as_timestamp)

with same output

user_52219440
Related