{"id":3337,"date":"2011-03-03T09:38:10","date_gmt":"2011-03-03T07:38:10","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3337"},"modified":"2011-03-03T09:41:27","modified_gmt":"2011-03-03T07:41:27","slug":"generating-google-line-charts-with-sql-part-ii","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/generating-google-line-charts-with-sql-part-ii","title":{"rendered":"Generating Google line charts with SQL, part II"},"content":{"rendered":"<p>This post continues <a href=\"http:\/\/code.openark.org\/blog\/mysql\/generating-google-line-charts-with-sql-part-i\">Generating Google line charts with SQL, part I<\/a>, in pursue of generating time series based image charts.<\/p>\n<p>We ended last post with the following chart:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Sample SQL Google Chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL\" alt=\"\" width=\"400\" height=\"200\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL\r\n<\/pre>\n<\/blockquote>\n<p>which has a nice curve, and a proper y-legend, but incorrect x-legend and no ticks nor grids.<\/p>\n<p>To date, Google Image Charts do not support time-series charts. We can&#8217;t just throw timestamp values and expect the chart to properly position them. We need to work these by hand.<\/p>\n<p>This is not easily done; if our input consists of evenly spread timestamp values, we are in a reasonable position. If not, what do we do?<\/p>\n<p>There are several solutions to this:<\/p>\n<ul>\n<li>We can present whatever points we have on the chart, making sure to position them properly. This makes for an uneven distribution of ticks on the x-axis, and is not pleasant to watch.<\/li>\n<li>We can extrapolate values for round hours (or otherwise round timestamp resolutions), and so show evenly spread timestamps. I don&#8217;t like this solution one bit, since we&#8217;re essentially inventing values here. Extrapolation is nice when you know you have nice curves, but not when you&#8217;re doing database monitoring, for example. You must have the precise values.<\/li>\n<li>We can do <a href=\"http:\/\/en.wikipedia.org\/wiki\/Oversampling\">oversampling<\/a>, then group together several measurements within round timestamp resolutions. For example, we can make a measurement every <strong>2<\/strong> minutes, yet present only <strong>6<\/strong> measurements per hour, each averaging up <strong>10<\/strong> round minutes. This is the approach I take with <a href=\"http:\/\/code.openark.org\/forge\/mycheckpoint\">mycheckpoint<\/a>.<\/li>\n<\/ul>\n<p>The latest approach goes even beyond that: what if we missed 30 minutes of sampling? Say the server was down. We then need to &#8220;invent&#8221; the missing timestamps. Note that we invent the timestamps, we do not invent values. We must present the chart with missing values on our invented timestamps.<\/p>\n<p>I may show how to do this in a future post. Meanwhile, let&#8217;s simplify and assume our values <em>are<\/em> evenly spread.<!--more--><\/p>\n<h4>Sample data<\/h4>\n<p>We use <a href=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2011\/03\/google_charts.sql_.txt\">google_charts.sql<\/a>. Note that the timestamp values provided in Part I of this post is skewed, so make sure to use this file.<\/p>\n<h4>x-axis values<\/h4>\n<p>We use <strong>chxl<\/strong> to present with x-axis values. We may be tempted to just list all values. Would that work?<\/p>\n<p>Sadly, no, for two reasons:<\/p>\n<ol>\n<li>Google is not smart enough; whatever we throw at it, it will try to present. So, if we have <strong>288<\/strong> rows, that&#8217;s <strong>288<\/strong> x-axis values. Not enough room, to be sure! Smarter implementations would automatically hide some values, so as only to present with non-overlapping values.<\/li>\n<li>Our URL will turn out to be too long. Remember: <strong>2048<\/strong> characters is our maximum limit for GET request!<\/li>\n<\/ol>\n<p>Also, we must format our timestamp to be of minimal width. In our example, we have a <strong>24<\/strong> hour range. We therefore present timestamps in hh:MM format. So, a naive approach would be to:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT(\r\n    'http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,',\r\n    ROUND(min_value, 1), ',',\r\n    ROUND(max_value, 1),\r\n    '&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    ),\r\n   '&amp;chxl=0:|',\r\n    <strong>GROUP_CONCAT(<\/strong>\r\n<strong>      DATE_FORMAT(ts, '%H:%i')<\/strong>\r\n<strong>      SEPARATOR '|'<\/strong>\r\n<strong>    )<\/strong>\r\n  ) FROM chart_data, chart_data_minmax<\/pre>\n<\/blockquote>\n<p>The resulting URL is just too long.<\/p>\n<p>Solution? Let&#8217;s only consider round hour timestamps! Our next attempt looks like this (we also throw in <strong>chxs<\/strong>, to show ticks):<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT(\r\n    'http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,',\r\n    ROUND(min_value, 1), ',',\r\n    ROUND(max_value, 1),\r\n    '&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    ),\r\n   <strong>'&amp;chxs=0,505050,10,0,lt',<\/strong>\r\n<strong>   '&amp;chxl=0:|',<\/strong>\r\n<strong>    GROUP_CONCAT(<\/strong>\r\n<strong>      IF(<\/strong>\r\n<strong>        MINUTE(ts) = 0,<\/strong>\r\n<strong>        DATE_FORMAT(ts, '%H:%i'),<\/strong>\r\n<strong>        NULL<\/strong>\r\n<strong>      )<\/strong>\r\n<strong>      SEPARATOR '|'<\/strong>\r\n<strong>    )<\/strong>\r\n  ) FROM chart_data, chart_data_minmax\r\n<\/pre>\n<\/blockquote>\n<p>and results with:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"SQL line chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&amp;chxs=0,505050,10,0,lt&amp;chxl=0:|00:00|01:00|02:00|03:00|04:00|05:00|06:00|07:00|08:00|09:00|10:00|11:00|12:00|13:00|14:00|15:00|16:00|17:00|18:00|19:00|20:00|21:00|22:00|23:00\" alt=\"\" width=\"400\" height=\"200\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&amp;chxs=0,505050,10,0,lt&amp;chxl=0:|00:00|01:00|02:00|03:00|04:00|05:00|06:00|07:00|08:00|09:00|10:00|11:00|12:00|13:00|14:00|15:00|16:00|17:00|18:00|19:00|20:00|21:00|22:00|23:00<\/pre>\n<\/blockquote>\n<p>Too messy, isn&#8217;t it?<\/p>\n<h4>A word about ticks<\/h4>\n<p>You would think: OK, then, let&#8217;s just present every <strong>4<\/strong> round hours timestamps. But there&#8217;s a catch: a tick will show only when there&#8217;s an x-axis value. It&#8217;s nice to have a tick for every hour, but we only want to present values every <strong>4<\/strong> hours.<\/p>\n<p>Fortunately, we can provide with an unseen value: a space (URL encoded as &#8216;<strong>+<\/strong>&#8216;). So we complicate things up a bit on the <strong>chxl<\/strong> to read:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT(\r\n    'http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,',\r\n    ROUND(min_value, 1), ',',\r\n    ROUND(max_value, 1),\r\n    '&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    ),\r\n   '&amp;chxs=0,505050,10,0,lt',\r\n   '&amp;chxl=0:|',\r\n    <strong>GROUP_CONCAT(<\/strong>\r\n<strong>      IF(<\/strong>\r\n<strong>        MINUTE(ts) = 0,<\/strong>\r\n<strong>        IF(<\/strong>\r\n<strong>          HOUR(ts) MOD 4 = 0,<\/strong>\r\n<strong>          DATE_FORMAT(ts, '%H:%i'),<\/strong>\r\n<strong>          '+'<\/strong>\r\n<strong>        ),<\/strong>\r\n<strong>        NULL<\/strong>\r\n<strong>      )<\/strong>\r\n<strong>      SEPARATOR '|'<\/strong>\r\n<strong>    )<\/strong>\r\n  ) FROM chart_data, chart_data_minmax<\/pre>\n<\/blockquote>\n<p>and get:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"SQL line chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&amp;chxs=0,505050,10,0,lt&amp;chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+\" alt=\"\" width=\"400\" height=\"200\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&chxs=0,505050,10,0,lt&chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+\r\n<\/pre>\n<\/blockquote>\n<h4>OK, I cheated<\/h4>\n<p>Who says sample data starts with a round hour? We have that hidden assumption here, since the first tick is necessarily a round hour in our code. Yet our data may start at <strong>12:35<\/strong>, for example. Sorry, you&#8217;ll have to dig into <a href=\"http:\/\/code.google.com\/p\/mycheckpoint\/source\/browse\/trunk\/src\/mycheckpoint.py\">mycheckpoint&#8217;s source code<\/a> to see a thorough solution. It&#8217;s just too much for this post.<\/p>\n<h4>Grids<\/h4>\n<p>Let&#8217;s wrap this up with grids. Grids work by specifying the step size (in percent of overall height\/width) and initial offset (again, in percent).<\/p>\n<p>Wouldn&#8217;t it be nicer if grids were automatically attached to ticks? I mean, REALLY! What were those guys thinking? (I know, they&#8217;re doing great work. Keep it up!)<\/p>\n<p>Problem is, I have no idea how Google chooses to distribute values on the y-axis. I don&#8217;t know where y-axis ticks will be placed. So on y-axis, I just choose to split charts to <strong>4<\/strong> even parts, and draw horizontal grids between them. Percent is <strong>25<\/strong> (<strong>100\/4<\/strong>), offset is <strong>0<\/strong>.<\/p>\n<p>But I do have control over the x-axis. In our case, I know how many ticks we&#8217;ll be having. Plus, I made life easier by assuming we start with a round hour, so no offset is required.<\/p>\n<p>Umm&#8230; How many ticks do we have? Easy: the number of round hours. This can be calculated by: <strong>SUM(MINUTE(ts) = 0<\/strong>. Actually, we need to take <strong>1<\/strong> off.<\/p>\n<p>We now build the <a href=\"http:\/\/code.google.com\/apis\/chart\/docs\/chart_params.html#gcharts_grid_lines\"><strong>chg<\/strong><\/a> parameter:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT(\r\n    'http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,',\r\n    ROUND(min_value, 1), ',',\r\n    ROUND(max_value, 1),\r\n    '&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    ),\r\n   '&amp;chxs=0,505050,10,0,lt',\r\n   '&amp;chxl=0:|',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        MINUTE(ts) = 0,\r\n        IF(\r\n          HOUR(ts) MOD 4 = 0,\r\n          DATE_FORMAT(ts, '%H:%i'),\r\n          '+'\r\n        ),\r\n        NULL\r\n      )\r\n      SEPARATOR '|'\r\n    ),\r\n   <strong>'&amp;chg=', ROUND(100.0\/((SUM(MINUTE(ts) = 0) -1)), 2), ',25,1,2,0,0'<\/strong>\r\n  ) FROM chart_data, chart_data_minmax\r\n<\/pre>\n<\/blockquote>\n<p>and get:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"SQL line chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&amp;chxs=0,505050,10,0,lt&amp;chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+&amp;chg=4.35,25,1,2,0,0\" alt=\"\" width=\"400\" height=\"200\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&chxs=0,505050,10,0,lt&chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+&chg=4.35,25,1,2,0,0\r\n<\/pre>\n<\/blockquote>\n<p>Phew!<\/p>\n<h4>Conclusion<\/h4>\n<p>So we haven&#8217;t worked on offsets. And, this is a single line chart. What about multiple lines? Legend? The following chart:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"SQL multi line chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=370x180&amp;chts=303030,12&amp;chtt=Latest+24+hours:+Nov+9,+05:50++-++Nov+10,+05:50&amp;chf=c,s,ffffff&amp;chdl=com_select_psec|com_insert_psec|com_delete_psec|com_update_psec|com_replace_psec&amp;chdlp=b&amp;chco=ff8c00,4682b4,9acd32,dc143c,9932cc&amp;chd=s:RTOSRORPNSSORROQTNQQMQRPQSOMUMPQOQUNRUQPUPSRQPUPSTRPQPPQQQQPPURPSQKUQPUQPUTPUQRUTRSRRWTRURRUPQUQSWTSUSOUURTRUTSPSRPPRMRRPRROQSNPQNPPSSRMQPQPQNRQV,MMMRQMPMNPOLOLLORLPOLQSLPRNNUNNQOPSNOQMNRNNPNMRMOQNNNOMNNLOPLPNNOOJPMMOOKRTMPONQNNQOLOMLOMLOKPXNMPMOOMKNNMPNMY976eMMRNQOOMKLOMKLKMMNKMOIPRJPMJMNK,EEDEEDEEDEFEEEDEFEEEDEFEEFEDEEEFEEEEEFEEEFEFEEFEEFEDEEEFEEEEEFEEEEEEFFEFFFEEEFFFFEEEFFFEEEEFDEEEEFFEEEEEFEEFFPx0xXFEEDEFEEEDEFEEEDEFEEEDEEDEEDEFF,FFEFFEFEEFFEFEEFFEFFEFGDEFDEFDEFEEGEEFEEFEEFEEFEEFEEEEEFEEFFDFEEFFDFEEFFEFEEFEEFEEFFDFEEFEDFEEFEEFDEFEDFEEFFENGDFEEFEEFEEFEEFFEFEEEFEFEEEFDFEEFGE,AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA&amp;chxt=x,y&amp;chxr=1,0,356.830000&amp;chxl=0:|+||08:00||+||12:00||+||16:00||+||20:00||+||00:00||+||04:00||&amp;chxs=0,505050,10,0,lt&amp;chg=4.17,25,1,2,0.69,0&amp;chxp=0,0.69,4.86,9.03,13.20,17.37,21.54,25.71,29.88,34.05,38.22,42.39,46.56,50.73,54.90,59.07,63.24,67.41,71.58,75.75,79.92,84.09,88.26,92.43,96.60&amp;tsstart=2010-11-09+05:50:00&amp;tsstep=600\" alt=\"\" width=\"370\" height=\"180\" \/>\r\n<\/pre>\n<\/blockquote>\n<p>is harder to achieve. I&#8217;m leaving this up to you!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post continues Generating Google line charts with SQL, part I, in pursue of generating time series based image charts. We ended last post with the following chart: http:\/\/chart.apis.google.com\/chart?cht=lc&#038;chs=400&#215;200&#038;chtt=SQL%20chart&#038;chxt=x,y&#038;chxr=1,-4716.6,5340.0&#038;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL which has a nice curve, and a proper y-legend, but incorrect x-legend and no ticks nor grids. To date, Google Image Charts do not support time-series [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[41,49,21],"class_list":["post-3337","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-mycheckpoint","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-RP","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3337"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3337\/revisions"}],"predecessor-version":[{"id":3358,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3337\/revisions\/3358"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}