Timestamp objects sent via BigQuery libraries return error "This field is not a record"

Question

When sending date fields as objects to BigQuery tables that are typed as timestamps, the google java API library does not throw an exception but no data lands. Examining the "InsertAllResponse" response type returns includes the error "This field is not a record".

eg

Hashmap<String,Object> rowContent = new Hashmap<>();
rowContent.put("Time", new Date());
rowContent.put("Name", "Harry");

and then

BigQuery bq = BigQueryOptions.getDefaultInstance().getService();
TableId tableId = TableId.of(datasetName, tableName);
InsertAllRequest.Builder insertReqBuilder = InsertAllRequest.newBuilder(tableId);
insertReqBuilder.addRow({some string}, row);
InsertAllResponse response = bigquery.insertAll(insertReqBuilder.build());

returns a response.hasErrors() true.

Also reported for python here and firebase here and javascript here

Answer 1

It appears that sending dates as objects causes the client API to create a JSON record rather than a single field (and this suggests also that the datetime typing has not been explicitly mapped, so timezone issues may be introduced).

Instead, send the date/time as UTC seconds since 1970, ie modify the above:

Hashmap<String,Object> rowContent = new Hashmap<>();
rowContent.put("Time", Math.floor(new Date().getTime()/1000));
rowContent.put("Name", "Harry");

(NB: not sure how to cope with milliseconds, see eg BigQuery not dealing with timestamp in millisecond with partition column , I'll find out and get back)