在Google融合表中编写查询以比较时间

本文介绍了在Google融合表中编写查询以比较时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想从google融合表中获取所有满足给定日期条件的条目 时间栏>特定时间

I want to get all the entries from google fusion table which satisfies the condition in a given date Time column > specific time

www.googleapis/fusiontables/v2/query?sql=SELECT * FROM 1WjowbI77j1WFcn3IEtbwBymhVZh8jfmP_dg1epd9 WHERE Date = '2015-02-23' AND Time > '10:25:04'&key=AIzaSyCALoSz00ZY3zTL1D_xUTD9GMb3T1ocBdU

但是它给了我所有的输入结果.

But it gives me all the entries as result..

融合表:

www.google/fusiontables/data?docid=1WjowbI77j1WFcn3IEtbwBymhVZh8jfmP_dg1epd9&key=AIzaSyCALoSz00ZY3zTL1D_xUTD9GMb3T1ocBdU#rows:id=1

推荐答案

假定Time列的类型为Date/Time，格式为H:mm:ss AM/PM

It is assumed that the type of Time column is Date/Time and format is H:mm:ss AM/PM

在这种情况下，不似乎不支持对Date/Time列的过滤.

In that case, it seems the filtering on Date/Time column is not supported.

根据行和查询SQL参考文档:

在DATETIME进行过滤

在类型为DATETIME的列上进行过滤时，<value>应该为 格式化为以下受支持的格式之一:

When filtering on a column of type DATETIME, the <value> should be formatted as one of the following supported formats:

MMM dd, yy MM/dd/yy MM-dd-yy MMM-dd-yy yyyy.MM.dd dd-MMM-yy MMM/yy MMM yy dd/MMM/yy yyyy

话虽如此，您可以考虑对返回的结果应用过滤，如以下JavaScript示例所示:

Having said that, you could consider to apply filtering to the returned results as demonstrates the following JavaScript example:

var key = 'AIzaSyCALoSz00ZY3zTL1D_xUTD9GMb3T1ocBdU' var sql = "SELECT * FROM 1WjowbI77j1WFcn3IEtbwBymhVZh8jfmP_dg1epd9 WHERE Date = '2015-02-23'"; var requestUrl = "www.googleapis/fusiontables/v2/query?sql=" + sql + "&key=" + key; var timeKey = '10:25:04'; $.getJSON(requestUrl, function(data) { var filteredRows = data.rows.filter(function(row){ var dtCur = Date.parse(row[3] + ' ' + row[7]); var dtKey = Date.parse(row[3] + ' ' + timeKey); if (dtCur > dtKey) { return row; } }); //print var output = JSON.stringify(filteredRows, null, 2); $("#output").text(output); });

<script src="ajax.googleapis/ajax/libs/jquery/2.1.1/jquery.min.js"></script> <pre style="background-color: #c0c0c0" id="output"></pre>