influxDB sql

迈不过友情╰ 2022-05-21 01:08 180阅读 0赞

#  #

# [influxdb0.13命令][influxdb0.13] #

1、数据构成

INSERT cpu_load_short,host=server01,region=us-west value=0.64,value2=0.86 1434055562000000000

第一部分：“cpu\_load\_short,host=server01,region=us-west”

第一部分称为key，key中包含了measurement name（类似表）和tags（tags又分为tag key和tag value，tags可以有多个）

注意：在tag value中的空格应以“\\”加上空格表示，tags中的值必须是string类型，其实是起到索引的作用

第二部分：“value=0.64,value2=0.86”

第二部分称为Field，同样和tags的形式相同，都是键值对的形式，但是tags中的值必须是string类型，而Field中的值可以为Integer、float、Boolean、string类型，

若为Integer类型，则值后必须加“i”，否则该值为float类型，

比如value=23意味着这个值23是float类型，

而value=23i，意味着值23是Integer类型。

Boolean类型的值的表示方式有很多，直接写成：t, T, true, TRUE, f, F, false或 FALSE都可以。

第三部分（可选）：“1434055562000000000”  
第三部分称为Timestamp，是时间戳，如果该部分省略，则默认将当前时间的时间戳插入[数据库][Link 1]，否则按照用户输入的时间戳插入。

注意：influxdb默认使用UTC时区展示数据

2、创建及使用数据库

CREATE DATABASE "testDB"  --创建数据库
    show databases  --展示所有数据库
    use testDB使用  --数据库

3、增删改查命令

![复制代码][copycode.gif]

查询表信息 SHOW MEASUREMENTS --查询当前数据库中含有的表 SHOW FIELD KEYS --查看当前数据库所有表的字段 SHOW series from pay --查看key数据 SHOW TAG KEYS FROM "pay" --查看key中tag key值 SHOW TAG VALUES FROM "pay" WITH KEY = "merId" --查看key中tag 指定key值对应的值 SHOW TAG VALUES FROM cpu WITH KEY IN ("region", "host") WHERE service = 'redis' DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>' --删除key SHOW CONTINUOUS QUERIES --查看连续执行命令 SHOW QUERIES --查看最后执行命令 KILL QUERY <qid> --结束命令 SHOW RETENTION POLICIES ON mydb --查看保留数据 查询数据 SELECT * FROM /.*/ LIMIT 1 --查询当前数据库下所有表的第一行记录 select * from pay  order by time desc limit 2 select * from db_name."POLICIES name".measurement_name --指定查询数据库下数据保留中的表数据 POLICIES name数据保留 删除数据 delete from "query" --删除表所有数据，则表就不存在了 drop MEASUREMENT "query"  --删除表（注意会把数据保留删除使用delete不会） DELETE FROM cpu DELETE FROM cpu WHERE time < '2000-01-01T00:00:00Z' DELETE WHERE time < '2000-01-01T00:00:00Z' DROP DATABASE “testDB” --删除数据库 DROP RETENTION POLICY "dbbak" ON mydb --删除保留数据为dbbak数据 DROP SERIES from pay where tag_key='' --删除key中的tag SHOW SHARDS --查看数据存储文件 DROP SHARD 1 SHOW SHARD GROUPS SHOW SUBSCRIPTIONS

![复制代码][copycode.gif]

4、函数使用

![复制代码][copycode.gif]

mean-平均值
    sum-总和
    min-最小值
    max-最大值
    count-总个数
    select * from pay   order by time desc limit 2
    select mean(allTime) from pay where time >= today() group by time(10m) time_zone(+8)
    select * from pay time_zone(+8) limit 2 
    SELECT sum(allTime) FROM "pay" WHERE time > now() - 10s
    select count(allTime) from pay  where time > now() - 10m  group by time(1s)

![复制代码][copycode.gif]

5、用户管理命令

![复制代码][copycode.gif]

SHOW USERS
    CREATE USER jdoe WITH PASSWORD '1337password' -- Create a normal database user.
    CREATE USER jdoe WITH PASSWORD '1337password' WITH ALL PRIVILEGES -- Create an admin user.
    REVOKE ALL PRIVILEGES FROM jdoe revoke admin privileges from jdoe
    REVOKE READ ON mydb FROM jdoe -- revoke read privileges from jdoe on mydb
    SHOW GRANTS FOR jdoe -- show grants for jdoe
    GRANT ALL TO jdoe -- grant admin privileges
    GRANT READ ON mydb TO jdoe -- grant read access to a database
    DROP USER jdoe

![复制代码][copycode.gif]

6、数据保留命令

![复制代码][copycode.gif]

查看保留期 SHOW RETENTION POLICIES ON mydb
    修改保留期 ALTER RETENTION POLICY default    ON online   DEFAULT
    删除保留期 DROP RETENTION POLICY <retentionpolicy> ON <database>
    创建保留期 CREATE RETENTION POLICY "rp_name" ON "db_name" DURATION 30d REPLICATION 1 DEFAULT

1.  1.  rp\_name：策略名
    2.  db\_name：具体的数据库名
    3.  30d：保存30天，30天之前的数据将被删除  
        它具有各种时间参数，比如：h（小时），w（星期）`m` minutes `h` hours `d` days `w` weeks `INF` infinite
    4.  REPLICATION 1：副本个数，这里填1就可以了
    5.  DEFAULT 设为默认的策略

![复制代码][copycode.gif]

7、创建持续性数据处理结果 提供后续查询

![复制代码][copycode.gif]

-- selects from default retention policy and writes into 6_months retention policy
    CREATE CONTINUOUS QUERY "10m_event_count"
    ON db_name
    BEGIN
      SELECT count(value)
      INTO "6_months".events
      FROM events
      GROUP BY time(10m)
    END;
    
    -- this selects from the output of one continuous query in one retention policy and outputs to another series in another retention policy
    CREATE CONTINUOUS QUERY "1h_event_count"
    ON db_name
    BEGIN
      SELECT sum(count) as count
      INTO "2_years".events
      FROM "6_months".events
      GROUP BY time(1h)
    END;
    
    -- this customizes the resample interval so the interval is queried every 10s and intervals are resampled until 2m after their start time
    -- when resample is used, at least one of "EVERY" or "FOR" must be used
    CREATE CONTINUOUS QUERY "cpu_mean"
    ON db_name
    RESAMPLE EVERY 10s FOR 2m
    BEGIN
      SELECT mean(value)
      INTO "cpu_mean"
      FROM "cpu"
      GROUP BY time(1m)
    END;
    DROP CONTINUOUS QUERY <cq_name> ON <database_name>  --删除

SHOW CONTINUOUS QUERIES --查看连续执行命令 ================================================ 案例：根据tags查询交易成功与失败笔数，并保存到一个表中，每分钟统计1分钟内的 CREATE CONTINUOUS QUERY fail ON online 　　BEGIN SELECT count(allTime) as fail INTO online."default".sign_result FROM online."default".sign 　　where orderFlag='0' 　　GROUP BY time(1m) END CREATE CONTINUOUS QUERY success ON online 　　BEGIN SELECT count(allTime) as success INTO online."default".sign_result FROM online."default".sign 　　where orderFlag='1' 　　GROUP BY time(1m) END > select * from sign_result name: sign_result ----------------- time            fail    success 1478053740000000000    2    2 1478053800000000000    3    3 1478053860000000000    1    1 1478053920000000000    3    1

![复制代码][copycode.gif]

8、http api``

<table style="border-spacing:0px;border:1px solid rgb(192,192,192);background:none;float:none;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:12px;min-height:auto;"> 
 <tbody style="background:none;float:none;line-height:1.1em;vertical-align:baseline;min-height:auto;"> 
  <tr style="border-top:0px;background:none;float:none;line-height:1.1em;vertical-align:baseline;min-height:auto;"> 
   <td style="border-color:rgb(192,192,192);border-collapse:collapse;color:rgb(175,175,175);line-height:1.1em;background:none;float:none;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;"> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      1 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      2 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      3 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      4 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      5 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      6 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      7 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      8 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      9 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      10 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      11 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      12 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      13 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      14 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      15 
    </div> 
    <div style="border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      16 
    </div> 
    <div style="background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;line-height:1.8em;text-align:right;vertical-align:baseline;min-height:auto;white-space:nowrap;">
      17 
    </div></td> 
   <td style="border-color:rgb(192,192,192);border-collapse:collapse;line-height:1.1em;background:none;float:none;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;"> 
    <div style="background:none;border:0px;float:none;line-height:1.1em;vertical-align:baseline;min-height:auto;"> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">1. 普通保存&lt;br&gt;curl -i -X POST&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'http://127.0.0.1:8086/write?db=online'</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-binary&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'pay,host=1,merId=1234567890,orderFlag=1 allTime=347,ecifTime=39,icqTime=88'</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&lt;strong&gt;2.Write points&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">from</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">a file&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">by</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">passing @filename to curl.&lt;/strong&gt;</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">cpu_data.txt内容如下：</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;">
       &nbsp; 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">cpu_load_short,host=server02 value=0.67</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">cpu_load_short,host=server02,region=us-west value=0.55 1422568543702900257</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">cpu_load_short,direction=</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">in</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">,host=server01,region=us-west value=2.0 1422568543702900257</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;">
       &nbsp; 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">Write the data&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">in</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">cpu_data.txt to the mydb database with:</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">curl -i -XPOST&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'http://localhost:8086/write?db=mydb'</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-binary @cpu_data.txt</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&lt;strong&gt;3.单查询&lt;/strong&gt;&lt;br&gt;curl -GET&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'http://localhost:8086/query?pretty=true'</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"db=mydb"</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"q=SELECT value FROM cpu_load_short WHERE region='us-west'"</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&lt;strong&gt;4.多查询&lt;/strong&gt;</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">curl -G&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'http://localhost:8086/query?pretty=true'</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"db=mydb"</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"q=SELECT value FROM cpu_load_short WHERE region='us-west';SELECT count(value) FROM cpu_load_short WHERE region='us-west'"</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&lt;strong&gt;5.格式化time&lt;/strong&gt;</code> 
     </div> 
     <div style="border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">epoch=[h,m,s,ms,u,ns]</code> 
     </div> 
     <div style="background:rgb(244,244,244) none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;min-height:auto;white-space:nowrap;"> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">curl -G&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">'http://localhost:8086/query'</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"db=mydb"</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"epoch=s"</code>&nbsp; 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">--data-urlencode&nbsp;</code> 
      <code style="background:none;border:0px;float:none;line-height:1.8em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">"q=SELECT value FROM cpu_load_short WHERE region='us-west'"</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

注意：如果是自己程序生成时间戳，进行数据保存后，查询时使用用select count（\*） from pay进行查询总条数时，需要确认一下influxdb数据库时间与程序生成数据的机器时间，因为查询不添加时间条件默认采用当前系统时间，所以就会造成数据无法做到实时入库，数据查询总是延后；

9、常用命令

9.1 转化查询结果数据time格式

precision rfc3339

![复制代码][copycode.gif]

> select * from sign
    name: sign
    ----------
    time            allTime    ecifTime    host        icqTime    icqTime1    merId        orderFlag
    1479880151976609227    348    0        195.203.56.35    0    0        305110099990002    null
    1479880301566372997    724    0        195.203.56.35    641    0        305110048163089    0
    1479880846739979577    28    0        195.203.56.35    12    0        305110099990002    0
    1479881595261796657    25    0        195.203.56.35    10    0        305110099990002    0
    1479881617138308807    106    0        195.203.56.35    17    0        305110099990002    0
    
    > precision rfc3339
    > select * from sign
    name: sign
    ----------
    time                            allTime    ecifTime    host        icqTime  icqTime1   merId          orderFlag
    2016-11-23T05:49:11.976609227Z      348    0        195.203.56.35    0        0        305110099990002    null
    2016-11-23T05:51:41.566372997Z      724    0        195.203.56.35    641      0        305110048163089    0
    2016-11-23T06:00:46.739979577Z       28    0        195.203.56.35    12       0        305110099990002    0
    2016-11-23T06:13:15.261796657Z       25    0        195.203.56.35    10       0        305110099990002    0
    2016-11-23T06:13:37.138308807Z      106    0        195.203.56.35    17       0        305110099990002    0

![复制代码][copycode.gif]

9.2按时间分组统计数据（分组只能用time()注意空格）

select count(allTime) from pay  where time > now() - 15h group by time(1h)

9.3按指定时间段查询数据

select count(allTime),mean(allTime) from pay  where  time>='2016-11-30T16:00:00Z'and time<='2016-12-01T16:59:59Z' and orderFlag='1'

9.4脚本执行数据格式

influx -execute "select count(allTime),mean(allTime) from pay  
    where  time>='2016-12-10T16:00:00Z'and time<='2016-12-11T16:59:59Z' and orderFlag='1' " -database 'online'; 查询2016-12-11全天数据
    格式： influx -execute "sql" -database 'databasename'

注意如果自己程序生成的时间戳作为time，则需要注意查询出的数据时间相差8小时，所以查某一天的数据需要减掉8小时，如上

[influxdb0.13]: http://www.cnblogs.com/atwanli/articles/5732022.html
[Link 1]: http://lib.csdn.net/base/mysql
[copycode.gif]: /images/20220521/8cc362db429c41cfafc92f888f4b54b5.png