Minimum of Timestamp BigQuery Standard SQL

Question

I get the following error when running this simple query in Bigquery with standard SQL:

SELECT MIN(created) as mm FROM `projectId.ds.User`

Column created has type cloud.helix.Timestamp, which differs from the expected type of INT64 Dismiss COMPOSE QUERY

The created field has datatype Timestamp, is nullable, but contains no null values.

This query works however in Legacy SQL:

SELECT MIN(created) as mm FROM [projectId:ds.User]

Any advice?

Are you using the same timestamp data type for both? If DT string was used in legacy to store pre-1970 dates, then using Unix now will throw an error... — JohnHC
The tables are the same. In fact there is only one table called User but different syntax to access it. With BigQuery you set a flag 'Use Legacy SQL' to switch your query syntax. The exact same TIMESTAMP datatype is being used (as far as I can tell as a user). — Paul Bendevis
What is even more spooky is that I can run that Modern SQL query on with no error another one of my tables, which has the same created field with datatype TIMESTAMP and nullable. — Paul Bendevis
Have you tried select min(cast(created as Date)) as mm? If that one falls over, might be a piece of duff data — JohnHC
Something is generating a legacy timestamp representation that the standard SQL engine doesn't expect to see. You may want to star the issue on the public tracker for updates and I will also try to follow up here when I know more. — Elliott Brossard

Paul Bendevis Paul Bendevis · Accepted Answer · 2016-12-15T17:48:35