在Google融合表中编写查询以比较时间 [英] Write Query to Compare time in google fusion table

问题描述

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

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

https://www.googleapis.com/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..

融合表:

https://www.google.com/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 = "https://www.googleapis.com/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="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<pre style="background-color: #c0c0c0" id="output"></pre>

这篇关于在Google融合表中编写查询以比较时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！