Write Query to Compare time in google fusion table

Question

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..

fusion table :

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

Vadim Gremyachev Vadim Gremyachev · Accepted Answer · 2015-06-07T20:27:53

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

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

According to Row and Query SQL Reference documentation:

Filtering on DATETIME

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

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>

Write Query to Compare time in google fusion table

2 Answers