How do I create partitioned table based on timestamp column in BQ

Question

In the following table there is only two columns(ts and transaction_id) and 3 rows. I just tried to create a partitioned table based on timestamp column but I get error message.

create table myfirstdataset.partition_table1(ts timestamp, transaction_id int64)partition by ts as
select timestamp '2013-12-31 01:55:02.000 UTC' ts, 10 transaction_id union all
select timestamp '2013-12-31 02:10:02.000 UTC' ts, 5 transaction_id union all
select timestamp '2013-12-31 04:20:02.000 UTC' ts, 6 transaction_id 

Error: PARTITION BY expression must be DATE(<timestamp_column>) or else a DATE column

Answer 1

Should be PARTITION BY DATE(ts) instead of PARTITION BY ts - see CREATE TABLE statement and partition_expression in particular

create table myfirstdataset.partition_table1(ts timestamp, transaction_id int64)partition by date(ts) as
select timestamp '2013-12-31 01:55:02.000 UTC' ts, 10 transaction_id union all
select timestamp '2013-12-31 02:10:02.000 UTC' ts, 5 transaction_id union all
select timestamp '2013-12-31 04:20:02.000 UTC' ts, 6 transaction_id