Query Date

[Carl Stone]

I need a little help

Column A has the date and column C has the values, I need to keep the history in the spreadsheet but I am trying to select
 the previous 24 hours worth of data and present it into a line chart. 

The date being recorded is in this format '2013-06-25 07:00:00.000' but I'm struggling with my query.setQuery 
and I feel its a problem with my formatting

Can anyone recommend how I can construct my query, the code I'm using is below

Thanks

Carl

var d=new Date();
d.setDate(d.getDate()-1);


function drawVisualization() {
  
  var query = new google.visualization.Query(
      'https://docs.google.com/a/printpack.com/spreadsheet/ccc?key=keygoeshere');

  query.setQuery("select A,C where A >= datetime +d+ ");
 
 
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  visualization = new google.visualization.LineChart(document.getElementById('visualization'));
  var options ={
    width: 1500,height: 400
  }
  visualization.draw(data, options);
}

[asgallant]

I do not know exactly what the format you want is, but try this and see if it works:

var d = new Date();

d.setDate(dt.getDate() - 1);

var year = d.getFullYear();

var month = d.getMonth() + 1;

if (month.toString().length == 1) {

    month = '0' + month;

}

var day = d.getDate();

if (day.toString().length == 1) {

    day = '0' + day;

}

var hour = d.getDate();

if (hour.toString().length == 1) {

    hour = '0' + hour;

}

var minute = d.getDate();

if (minute.toString().length == 1) {

    minute = '0' + minute;

}

var second = d.getDate();

if (second.toString().length == 1) {

    second = '0' + second;

}

var microsecond = d.getDate();

while (microsecond.toString().length < 3) {

    microsecond = '0' + microsecond;

}

var dateString = year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second + '.' + microsecond;

var query = new google.visualization.Query('https://docs.google.com/a/printpack.com/spreadsheet/ccc?key=keygoeshere');

query.setQuery("select A,C where A >= datetime " + dateString);

[Carl Stone]

Sorry it didn't work

It threw al load of exceptions, any other suggestions. Really pulling my hair out

[Carl Stone]

Sorry it didn't work

Throws a very long exception.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/QmOgyp_8UMc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[asgallant]

Where is the exception thrown?  What error message do you get?

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.

[Carl Stone]

Sorry I don't pretend to a programmer. I dissected your code a little, so I could understand it a bit more. Also changed the final query a little and this is what I came up with.

var d=new Date();
d.setDate(d.getDate() - 1);

var year = d.getFullYear();  
var month = d.getMonth() + 1;

var day = d.getDate();
var hour = d.getDate();
var minute = d.getDate();
var second = d.getDate();
var microsecond = d.getDate();

if (month.toString().length == 1) {
    month = '0' + month;
}

if (day.toString().length == 1) {
    day = '0' + day;
}

if (hour.toString().length == 1) {
    hour = '0' + hour;
}

if (minute.toString().length == 1) {
    minute = '0' + minute;
}

if (second.toString().length == 1) {
    second = '0' + second;
}

while(microsecond.toString().length < 3) {

    microsecond = '0' + microsecond;
}

var dateString = year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + minute+ '.' + microsecond;

//var d = '2013-06-26 07:00:00.000';
//d.setDate(d.getDate()-1);
alert(dateString);

function drawVisualization() {
  
  var query = new google.visualization.Query(

      'https://docs.google.com/a/printpack.com/spreadsheet/ccc?key=keycode here');

  // Apply query language.
 query.setQuery("select A,C where A >= datetime  '" + dateString + "' ");

This is the error I'm now getting 

 

[asgallant]

*facepalm*  I see the problem.  Hour, minute, second, and millisecond are all pulled from the date wrong.  Replace them with these:

var d=new Date();
d.setDate(d.getDate() - 1);

var year = d.getFullYear();  
var month = d.getMonth() + 1;
var day = d.getDate();

var hour = d.getHours();
var minute = d.getMinutes();
var second = d.getSeconds();
var millisecond = d.getMilliseconds();

if (month.toString().length == 1) {
    month = '0' + month;
}
if (day.toString().length == 1) {
    day = '0' + day;
}
if (hour.toString().length == 1) {
    hour = '0' + hour;
}
if (minute.toString().length == 1) {
    minute = '0' + minute;
}
if (second.toString().length == 1) {
    second = '0' + second;
}

while(millisecond.toString().length < 3) {
    millisecond = '0' + millisecond;
}

var dateString = year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + minute+ '.' + millisecond;

[Carl Stone]

I just spotted it myself, but I cheated on the Milliseconds cause I didn't know the code

Its now working and thanks for your help, certainly been a learning curve!

var d=new Date();
d.setDate(d.getDate() - 1);

var year = d.getFullYear();  
var month = d.getMonth() + 1;
var day = d.getDate();
var hour = d.getHours();
var minute = d.getMinutes();
var second = d.getSeconds();

var microsecond = d.getDate();

if (month.toString().length == 1) {
    month = '0' + month;
}

if (day.toString().length == 1) {
    day = '0' + day;
}

if (hour.toString().length == 1) {
    hour = '0' + hour;
}

if (minute.toString().length == 1) {
    minute = '0' + minute;
}

if (second.toString().length == 1) {
    second = '0' + second;
}

//while(microsecond.toString().length < 3) {
//    microsecond = '0' + microsecond;
//}

var dateString = year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + minute+ '.' + '000';