Google Visualization api を使ってGoogle スプレッドシートに入れた情報を表示させています。スプレッドシートのD列には「2012/05/15」のような日付けが入っていますので、select * where D < now() などとsql文を書いて行を選択しています。

さて、ここからが質問です。
今月の行を選択するには、select * where month(now()) = month(D)";でできたのでが、今日から「30日前」や「2週間前」を選択するsql文がわりません。今日から「30日前」は、select * where D >= date 'today' - integer '30'";としてみたのですがダメでした。正しいsql文をご教示いただけませんか?

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/05/18 18:48:03
  • 終了:2012/05/21 07:38:00

ベストアンサー

id:Cherenkov No.1

Cherenkov回答回数1502ベストアンサー獲得回数4922012/05/20 08:31:37

ポイント200pt

Query Languageで日付の作り方がわからなかったのでJSで日付を作ってます。
→dateDiff()を使えば解決した。ソースのコメント参照。


30日前で検索。

<html>
<head>
<meta charset="utf-8">
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load("visualization", "1");
</script>
<script>
//Google Spreadsheets を簡易 SQL DB に!「Google Visualization API」 - WebOS Goodies
//http://webos-goodies.jp/archives/51310352.html
function handleResponse(response) {
  var data = response.getDataTable();
  var csv  = [];
  for (var row = 0; row < data.getNumberOfRows(); row++) {
    var line = [];
    for (var col = 0; col < data.getNumberOfColumns(); col++)
      line.push(data.getFormattedValue(row, col));
    csv.push(line.join(","));
  }
  alert(csv.join("\n"));
}
/*
//getNdate(2)で2日後。getNdate(-5)で5日前
//return ex "2012-5-18"
function getNdate(n) {
	var d = new Date(new Date().getTime() + (86400000 * n));
	var nd = [d.getFullYear(), d.getMonth()+1, d.getDate()].join('-');
	return nd;
}
 */
var url = 'GoogleスプレッドシートURL';
var query = new google.visualization.Query(url);
//D列の値が現在日時から30日前ならば、その行を取得。GMTなので注意。
query.setQuery('where dateDiff(D, now()) = -30');
//過去に提案したもの
//query.setQuery('where D = date "' + getNdate(-30) + '"');
query.send(handleResponse);
</script>
</head>
<body>
</body>
</html>

参考

他5件のコメントを見る
id:Cherenkov

where dateDiff(D,now()) = -30 こっちのほうが自然でした。

2012/05/21 03:43:49
id:ozaki

dateDiffを使うんですね。いろいろ調べていただいてありがとうございました。これで当初の目的を達することができました。感謝です!!

2012/05/21 07:37:48
  • id:taknt
    select * where D >= now() - integer '30';

    とか?
  • id:taknt
    select * where D >= TO_DAYS(NOW() - INTERVAL 30 DAY)
    とか
  • id:ozaki
    ご教示いただいた2つのsql文を使ってみたのですが、該当する行が選択されませんでした・・・。
  • id:taknt
    select * where D >= now() - 30;

    とか。
  • id:taknt
    date 'today' - integer '30'

    これって

    date( 'today' - integer '30') とかには ならないかな?

    ま、30のあとに 入ってる " も 気になるけど。
  • id:ozaki
    申し訳ないのですが、教えていただいたsqlでは、目的のものを選択しないようです・・・。
  • id:taknt
    そうですか。残念ですね。

    ま、推測なので コメントで 失礼しましたが。
  • id:Cherenkov
    少しでも早く解決したいならコピペで動作確認ができるコードを提示するべきです。
    (SQL部分を改造するだけでテストができるように)
  • id:ozaki
    Cherenkovさん、初めまして。Javascriptのコードは下記です。


    google.load("jquery", "1");
    google.load("visualization", "1");

    function init(){
    mapDrawing();
    }
    google.setOnLoadCallback(init);

    ////////////////////////////////////////
    // マップ描画
    ////////////////////////////////////////
    var mapDrawing = function(){

    // 地図を描画
    var map_opts = {};
    map_opts.center = new google.maps.LatLng(35.681382, 139.766084); // 中心を暫定
    map_opts.mapTypeId = google.maps.MapTypeId.ROADMAP;
    map_opts.zoom = 7;
    map_opts.panControl = true;
    map_opts.zoomControl = true;
    map_opts.scaleControl = true;
    map_opts.streetViewControl = true;
    map_opts.overviewMapControl = true;
    map_opts.overviewMapControlOptions = { opened : true };
    var map = new google.maps.Map($("#map")[0], map_opts);

    // Google Visualization APIを使って Google Spreadsheetからデータ取得
    var query_opts = {}
    query_opts.dataSource = "https://docs.google.com/spreadsheet/ccc?key=0Aojk_zIECowMdGltUGdmejJhZTVGbmVzRWRHMlhVc1E#gid=0";
    // /spreadsheet/cccでも読み込める


    query_opts.sql = "select * ";


    var query = new google.visualization.Query(query_opts.dataSource);
    query.setQuery(query_opts.sql);
    query.send(setMarker);

    // Markerを追加
    function setMarker(response){
    if (response.isError()){
    console.log(response.getDetailedMessage());
    return;
    }
    var data = response.getDataTable();
    var rows = data.getNumberOfRows();
    var cols = data.getNumberOfColumns();
    for (var row = 1; row < rows; row++){
    var mapinfo = {};
    mapinfo.title = data.getFormattedValue(row,2);
    mapinfo.descrption = data.getFormattedValue(row,15);
    // mapinfo.address = data.getFormattedValue(row,2);
    mapinfo.lat = data.getFormattedValue(row,17);
    mapinfo.lng = data.getFormattedValue(row,18);
    mapinfo.startdate = data.getFormattedValue(row,3);
    // mapinfo.fax = data.getFormattedValue(row,6);
    // mapinfo.mail = data.getFormattedValue(row,7);
    mapinfo.url = data.getFormattedValue(row,4);
    // mapinfo.infoprov = data.getFormattedValue(row,9);
    createMarker(mapinfo);
    }
    }

    //////////////////////////////
    // Marker作成
    //////////////////////////////
    var createMarker = function(mapInfo){
    var marker_opts = {};
    marker_opts.map = map ;
    marker_opts.position = new google.maps.LatLng(mapInfo.lat, mapInfo.lng);
    marker_opts.title = mapInfo.title ;
    marker_opts.icon = "http://labs.google.com/ridefinder/images/mm_20_red.png";
    marker_opts.shadow = "http://labs.google.com/ridefinder/images/mm_20_shadow.png";
    marker_opts.animation = google.maps.Animation.DROP ;
    var marker = new google.maps.Marker(marker_opts);
    google.maps.event.addListener(marker, 'rightclick', function(){
    showInfoWindow(mapInfo, marker); });
    //クリックされたらズームする処理を追加
    google.maps.event.addListener(marker, 'click', function(){
    // ズームを変更
    marker_opts.map.setZoom(15);
    });

    };

    //////////////////////////////
    // InfoWindowを表示
    //////////////////////////////
    var infoWindow, infoWindow_opts;
    var showInfoWindow = function(mapInfo, marker){
    var content = '';
    //<div>で情報ウインドウの大きさを指定。参考/http://rnk.mitelog.jp/oyaji/2011/07/googlemap-api-v-5e49.html
    content += '<div id="infowindowclass"><b>' + mapInfo.title + '</b><br>';
    if (mapInfo.descrption) {
    content += "!!&nbsp;&nbsp;" + mapInfo.descrption + '<br>';
    }

    content += mapInfo.address + '<br>';
    if (mapInfo.pubdate) {
    content += "登録日:" + mapInfo.pubdate + '<br>';
    }
    /*
    if (mapInfo.startdate || mapInfo.fax) {
    content += "&nbsp;/&nbsp;";
    }
    if (mapInfo.fax) {
    content += "Fax:" + mapInfo.fax ;
    }
    if (mapInfo.pubdate || mapInfo.fax) {
    content += "<br>";
    }
    if (mapInfo.mail) {
    content += "[<a href='mailto:" + mapInfo.mail + "'>メール</a>]";
    }
    if (mapInfo.mail && mapInfo.url) {
    content += "&nbsp;";
    }
    */
    if (mapInfo.url) {
    content += "[<a href='" + mapInfo.url + "' target='_blank'>参照元サイトを見る</a>]";
    }
    if (mapInfo.mail || mapInfo.url) {
    content += "<br></div>";
    }
    /*
    if (mapInfo.infoprov) {
    content += "<div style='text-align:right;'>by " + mapInfo.infoprov + "</div>";
    }
    */

    if (infoWindow) { infoWindow.close(); }
    infoWindow_opts = {}
    infoWindow_opts.content = content ;
    infoWindow_opts.disableAutoPan = true ;
    infoWindow_opts.maxWidth = 400 ;
    infoWindow = new google.maps.InfoWindow(infoWindow_opts);
    map.setCenter(marker.getPosition());
    infoWindow.open(map, marker);
    };
    }

    ////////////////////////////////////////
    // HTMLエスケープ
    ////////////////////////////////////////
    var escapeHtml = function(text){
    return (text || '').replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/\u0022/g, '&quot;');
    };

    以上。

    コードの query_opts.sql = "select * "; 部分に「今日から30日前」などの指定をしたく思っています。

    データは、Googleスプレッドシートにあります(
    https://docs.google.com/spreadsheet/ccc?key=0Aojk_zIECowMdGltUGdmejJhZTVGbmVzRWRHMlhVc1E#gid=0 )

    最終的には、http://withmama.lolipop.jp/kaihatsu/spreadsheet_to_map/hatena.hml として地図に指定のものだけをマーカー付きで表示いたします。

    どうぞ、よろしくご教示ください。
  • id:Cherenkov
    Googleスプレッドシートを閲覧する権限をリクエストして承認してもらわないと参照できないようですね。
  • id:Cherenkov
    回答側が必要な情報はスプレッドシートの状態とどのようにクエリを投げているかです。
    今回の質問にgoogleマップ云々はノイズです。
    提示されたスプレッドシートの共有設定を公開にするか、最小限のデータに絞った新規ファイルを再度提示するとみんなが幸せになります。
    http://webos-goodies.jp/archives/51310352.html
    このページに埋め込まれているQuery Viewerを使えば最速で確認できるかも。
  • id:ozaki
    ゴメンナサイ。Googleスプレッドシートを公開いたしました。
  • id:Cherenkov
    回答1のwhere D = date "2012-5-20" といった書式で取得できますね。
    http://gyazo.com/e6cc04068ac9c742f437eb372ecf4242

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません