Invalid DateTime error while trying to insert datetime value into BigQuery from Dataflow

Question

We wrote a Google Data Flow code that inserts a value into a bigquery table whose column is of type DateTime. The logic was running fine most of the times. But suddenly we get Invalid DateTime issue.

Exception: java.lang.RuntimeException: java.io.IOException: Insert failed: [{"errors":[{"debugInfo":"generic::out_of_range: Invalid datetime string \"2017-09-26T21:16\"

It is unclear how and why the above value is invalid. We see that it is adhering to DateTime datatype mentioned in https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

Also it remains unclear why it is throwing this error only on occasions.

We wrote a custom Transformation code that extends DoFn The ProcessElement code goes like this

 public void processElement(ProcessContext c) throws Exception {

    TableRow tableRow = c.element();
    try {
       // do some processing then 
      tableRow.set("PredictedDate",**LocalDateTime.now().toString()**);
      c.output(tableRow);
    }catch(Exception exc){
        LOG.error("Exception while processing and hence not attempting to write to bigquery");
    }
} 

enter code here

It was working fine but fails occasionally during night times(US Central timezone). Can you please help us find the root cause.

Answer 1

The format described for DateTime indicates that a seconds field is required.

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]]

Specifically, note that the second S is not enclosed in square brackets making it optional.

Answer 2

In my case I'm retrieving time in timestamp and make convertion to the string:

new Date(event_timestamp_ms).toISOString().slice(0, -1);

Since DATETIME doesn't contain information about timezone, if helps to remove Z symbol from UTC format.