Hi, I think formatting timestamp in this case is reasonable. It would be great if we can push this patch.
(Should timezone need to be hardcoded?) Thanks, moon On Fri, Dec 15, 2017 at 3:31 AM Srikanth G N <sri...@sentienz.com> wrote: > As seen in the attached image, the select queries on bigquery table whose > output fields contain TIMESTAMP are not shown as Dates, which is the > expected behavior when compared to query outputs on say mysql databases. > > While debugging the issue (zeppelin 0.7.3 source, file > BigQueryInterpreter.java), found that bigquery api client is sending > timestamps as 1.512648528E9 this , which is rendered as it is. > > < > http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/file/t818/nowworkingcase.png > > > > > I applied this fix / logic > Capture the field type using TableSchema, if it has the TIMESTAMP field, > only then convert it to date field. Part of the fix is highlighted in bold. > > public static String printRows(final GetQueryResultsResponse response) { > StringBuilder msg = null; > msg = new StringBuilder(); > * int size=1000; > int i=0; > Boolean typeSchema[] = new Boolean[size]; > for ( i = 0; i < size; i++) { > typeSchema[i] = false; > } > i=0;* > > try { > for (TableFieldSchema schem: response.getSchema().getFields()) { > typeSchema[i++]= schem.getType().contains("TIMESTAMP"); > logger.error("srikgn : schema value : {} schema type {} bool is {} > i > is {}",schem.getName(), schem.getType(),typeSchema[i-1],i-1); > msg.append(schem.getName()); > msg.append(TAB); > } > msg.append(NEWLINE); > for (TableRow row : response.getRows()) { > i=0; > for (TableCell field : row.getF()) { > logger.error("srikgn : getV() {} field value : {} bool is {} i is > {}",field.getV(),field.getV().toString(),typeSchema[i],i); > * if(typeSchema[i++]) { > try { > long tsTest= > Double.valueOf(field.getV().toString()).longValue()*1000; > Date date = new Date(tsTest); > DateFormat format = new > SimpleDateFormat("yyyy/MM/dd > HH:mm:ss"); > format.setTimeZone(TimeZone.getTimeZone("Pacific/Truk")); > String formatted = format.format(date); > msg.append(formatted); > logger.info("dsf entered timestamp date is > {}",date); > } catch (Exception e) { > logger.error("Exception occured {}",e); > msg.append(field.getV().toString()); > } > > }* > else > msg.append(field.getV().toString()); > msg.append(TAB); > } > msg.append(NEWLINE); > } > return msg.toString(); > } catch ( NullPointerException ex ) { > throw new NullPointerException("SQL Execution returned an error!"); > } > } > > > So if such a fix is acceptable by the community, we woud like to push it, > because zeppelin is used by business people who will not know which is > field > is timestamp and wrap it with say DATE() fucntion in the query itself. > > So please suggest how can we overcome this problem > > > > > > > ----- > Srikanth G N > Sentienz > -- > Sent from: > http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/ >
