Yes that’s an option.
But then you get ts twice.
Can do that if you prefer.
Would be nice to have do
ts-local and * but not ts
Have not found how to do that
Sent from my iPhone using Tapatalk
indigo utilities discussion
Re: indigo utilities discussion
is this better?
Karl
Code: Select all
utilities print SQL data for device 375541516/wind sensor states: ['*']
SQL command= /usr/bin/sqlite3 -header -column '/Library/Application Support/Perceptive Automation/Indigo 7/logs/indigo_history.sqlite' "SELECT id, strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') as 'ts_local' , * from device_history_375541516 WHERE ID > 0 ORDER by id LIMIT 5;"
SQL-OUTPUT:
id ts_local id ts onoffstate avgspeed batterylevel directiondegrees directiontext display gust lastupdated signalstrength temperature type windchill
---------- ------------------- ---------- ------------------- ---------- ---------- ------------ ---------------- ------------- ---------- ---------- ----------- -------------- ----------- ---------- ----------
2 2014-06-28-11:45:32 2 2014-06-28 16:45:32 False
3 2014-06-28-11:45:48 3 2014-06-28 16:45:48 0 0 0 0 0 0 0 0
4 2014-06-28-11:45:58 4 2014-06-28 16:45:58 4.9 10 0 N 4 mph 2.7 2014/06/28 5 0 2 0
5 2014-06-28-11:46:12 5 2014-06-28 16:46:12 3.4 10 0 N 3 mph 2.7 2014/06/28 5 0 2 0
6 2014-06-28-11:46:40 6 2014-06-28 16:46:40 3.4 10 0 N 3 mph 6.9 2014/06/28 5 0 2 0
-
Colorado4Wheeler
- Posts: 2794
- Joined: Mon Jul 20, 2009 10:48 am
- Location: Colorado
Re: indigo utilities discussion
Hey, anything you do in regards to getting the system date displayed is A-OK with me Karl! That looks awesome, thank you!!
My Modest Contributions to Indigo:
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
-
Colorado4Wheeler
- Posts: 2794
- Joined: Mon Jul 20, 2009 10:48 am
- Location: Colorado
Re: indigo utilities discussion
Oh and there isn't a way to exclude a column from * through a query. The alternative would be to copy it to another table and report that (SELECT * INTO plugin_output and then DROP COLUMN) or to do a quick field check beforehand so that when the user selects * you interpret that as looping through all fields except TS. I'm an SQL developer and have been down this path before.
My Modest Contributions to Indigo:
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
Re: indigo utilities discussion
It is easier to do this on the result in python
Sent from my iPhone using Tapatalk
Sent from my iPhone using Tapatalk
Re: indigo utilities discussion
this https://www.dropbox.com/s/7q2wgce1eexuk ... 0.zip?dl=1
will produce this:
for sqlite
is this ok ? if yes will post to GitHub
Karl
reformatted the output in python (drop col 2, swap col 0,1)
will produce this:
Code: Select all
SQL command= /usr/bin/sqlite3 -header -column '/Library/Application Support/Perceptive Automation/Indigo 7/logs/indigo_history.sqlite' "SELECT strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') as 'ts_local' , * from device_history_375541516 WHERE ID > 0 ORDER by id LIMIT 10;"
SQL-OUTPUT:
id ts_local onoffstate avgspeed batterylevel directiondegrees directiontext display gust lastupdated signalstrength temperature type windchill
---------- ------------------- ---------- ---------- ------------ ---------------- ------------- ---------- ---------- ----------- -------------- ----------- ---------- ----------
2 2014-06-28-11:45:32 False
3 2014-06-28-11:45:48 0 0 0 0 0 0 0 0
4 2014-06-28-11:45:58 4.9 10 0 N 4 mph 2.7 2014/06/28 5 0 2 0
5 2014-06-28-11:46:12 3.4 10 0 N 3 mph 2.7 2014/06/28 5 0 2 0
6 2014-06-28-11:46:40 3.4 10 0 N 3 mph 6.9 2014/06/28 5 0 2 0
7 2014-06-28-11:46:54 3.4 10 0 N 3 mph 5.6 2014/06/28 5 0 2 0
8 2014-06-28-11:47:08 4.7 10 0 N 4 mph 3.6 2014/06/28 5 0 2 0
9 2014-06-28-11:47:22 4.7 10 0 N 4 mph 4.3 2014/06/28 5 0 2 0
10 2014-06-28-11:47:36 4.7 10 0 N 4 mph 5.4 2014/06/28 5 0 2 0
11 2014-06-28-11:48:04 4.9 10 0 N 4 mph 6 2014/06/28 5 0 2 0
utilities record id# 0 2 2014-06-28-11:45:32 False
is this ok ? if yes will post to GitHub
Karl
reformatted the output in python (drop col 2, swap col 0,1)
-
Colorado4Wheeler
- Posts: 2794
- Joined: Mon Jul 20, 2009 10:48 am
- Location: Colorado
Re: indigo utilities discussion
Genius! Looks awesome!
My Modest Contributions to Indigo:
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
Re: indigo utilities discussion
posted the final version on GitHub. Had to add code for "no header and/or no separator"
The trick to drop the regular TS column does not work if there are no columns in the output and no separator to id the columns (one of them is ok)
This is only for sqlite db and selection =*.
Time was already local for selection with specific columns and all of postgres
Karl
The trick to drop the regular TS column does not work if there are no columns in the output and no separator to id the columns (one of them is ok)
This is only for sqlite db and selection =*.
Time was already local for selection with specific columns and all of postgres
Karl
-
Colorado4Wheeler
- Posts: 2794
- Joined: Mon Jul 20, 2009 10:48 am
- Location: Colorado
Re: indigo utilities discussion
LOVE! Thanks Karl, it makes the report a lot more useful for me!
Code: Select all
SQL-OUTPUT:
id ts_local sensorvalue
---------- ------------------- -----------
1 2018-01-08-09:16:30 335.0
2 2018-01-08-09:17:11 335.0
3 2018-01-08-11:13:08 18.0
4 2018-01-08-11:13:31 376.0
5 2018-01-08-11:17:34 15.0
6 2018-01-08-11:18:03 371.0
7 2018-01-08-11:30:01 25.0
8 2018-01-08-11:30:31 362.0
9 2018-01-08-12:33:42 29.0
10 2018-01-08-13:31:51 378.0
11 2018-01-08-13:32:13 14.0
12 2018-01-08-14:32:51 229.0
13 2018-01-08-15:33:28 238.0
14 2018-01-08-16:34:06 221.0
15 2018-01-08-17:34:45 214.0
16 2018-01-08-18:35:23 216.0
17 2018-01-08-20:36:40 217.0
18 2018-01-08-21:37:20 218.0
19 2018-01-08-22:38:02 219.0
20 2018-01-08-23:12:44 0.0
21 2018-01-09-08:00:18 392.0
22 2018-01-09-09:00:19 186.0
23 2018-01-09-10:00:56 81.0
24 2018-01-09-11:01:35 22.0
25 2018-01-09-12:02:16 26.0
26 2018-01-09-13:02:52 21.0
My Modest Contributions to Indigo:
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy
Check Them Out Here
Re: indigo utilities discussion
Glad it works for you.
Sent from my iPhone using Tapatalk
Sent from my iPhone using Tapatalk
Re: indigo utilities discussion
released v 1.14.11
added option to print all indigo store plugins sorted with latest release date
looks like this:
Karl
added option to print all indigo store plugins sorted with latest release date
looks like this:
Code: Select all
Plugin Name web page ----------------------------------- Date released Version
piBeacon https://www.indigodomo.com/pluginstore/59/ Jan. 29, 2018 7.80.201
Sensibo https://www.indigodomo.com/pluginstore/146/ Jan. 29, 2018 0.1.5
NuVo Grand Concerto https://www.indigodomo.com/pluginstore/66/ Jan. 28, 2018 2.0.2
FindFriendsMini https://www.indigodomo.com/pluginstore/139/ Jan. 27, 2018 0.4.9
Multitool https://www.indigodomo.com/pluginstore/145/ Jan. 25, 2018 1.0.14
fingscan https://www.indigodomo.com/pluginstore/72/ Jan. 23, 2018 7.24.21
Announcements https://www.indigodomo.com/pluginstore/141/ Jan. 19, 2018 0.4.0
Shairport Sync https://www.indigodomo.com/pluginstore/144/ Jan. 18, 2018 1.0.0
Eversolar Monitor https://www.indigodomo.com/pluginstore/143/ Jan. 17, 2018 1.0.1
NetworkOwl https://www.indigodomo.com/pluginstore/142/ Jan. 16, 2018 1.6.1
Mac Apps https://www.indigodomo.com/pluginstore/92/ Jan. 14, 2018 0.0.6
...
Karl
Re: indigo utilities discussion
Whilst you have an option in utilities to output device names and ids to the log, it would be awesome if you could output the same to a postgres table as well, ie something with 2 columns, device_id, device_name? Or even do a generic one, so has columns of id, name and type, where type is device or variable?
Re: indigo utilities discussion
You mean creating a table in the indigo Postgres database? I would shy away from that. Indigo owns the dB. I don’t know what indigo would do if another tables is created.
Sent from my iPhone using Tapatalk
Sent from my iPhone using Tapatalk
Re: indigo utilities discussion
Yes I did... although indigo owns the database indigo_history or whatever its been called, what about creating in the postgres db, or the username one?
Re: indigo utilities discussion
Or I guess I could run it in a script, with a quick hack of asomethign found online, something along these lines?
import psycopg2
conn = psycopg2.connect(database = "userid", user = "postgres", password = "", host = "127.0.0.1", port = "5432")
cur = conn.cursor()
cur.execute('''CREATE TABLE DEVICES
(DEVICE_ID INT PRIMARY KEY NOT NULL,
DEVICE_NAME TEXT NOT NULL);''')
conn.commit()
for dev in indigo.devices:
cur.execute("INSERT INTO COMPANY (DEVICE_ID,DECIVE_NAME) \
VALUES (dev.id,dev.name )");
as as starting point?
import psycopg2
conn = psycopg2.connect(database = "userid", user = "postgres", password = "", host = "127.0.0.1", port = "5432")
cur = conn.cursor()
cur.execute('''CREATE TABLE DEVICES
(DEVICE_ID INT PRIMARY KEY NOT NULL,
DEVICE_NAME TEXT NOT NULL);''')
conn.commit()
for dev in indigo.devices:
cur.execute("INSERT INTO COMPANY (DEVICE_ID,DECIVE_NAME) \
VALUES (dev.id,dev.name )");
as as starting point?