[sqlite] Selecting multiple similar columnname.

2018-06-09 Thread Stephan Buchert
The GNSS systems do of course know when leap seconds occur, one of their
purposes is to communicate accurately UTC. But unlike system clocks of
computers which often are halted during leap seconds or slowed down before
them, to avoid complications, the satellites will transmit their codes
strictly also over leap seconds, and orbital information like elevation is
available in these. Therefore my advice to use on the Sqlite level a day
segmented two column time stamp in this particular case. It is also handy
if queries are for certain dates or date intervals, perhaps the performance
for such queries even increases compared to a single column time unit
counter?

The GNSS systems use internally their own time, one of them is GPS time. To
the consumer normally UTC is provided, which I assume is also here the
case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-09 Thread Keith Medcalf

No.  GPS time is maintained as WEEKS since the GPS epoch and an offset into the 
week (TOW -- time of week).  There is an additional field that is the "offset 
from UT1" (currently 11 seconds I believe).  The receiver uses the information 
to calculate UT1.  There is, I believe, a flag that says that the "offset" is 
to be incremented at the end of the current day, and that provides all the 
information necessary to calculate UT1, UTC, and the leap flag.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Saturday, 9 June, 2018 06:22
>To: SQLite mailing list
>Subject: Re: [sqlite] Selecting multiple similar columnname.
>
>So the satellites don't get leapsecond updates? I don't understand
>why it
>can't just be an epoch…
>
>On Sat, Jun 9, 2018, 8:39 AM Stephan Buchert 
>wrote:
>
>> To take care of the leap seconds every ~1.5 years or so, you need a
>day
>> segmented time stamp and a three column primary key:
>>
>> CREATE TABLE satlog (
>> sat INTEGER,
>> day2000 INTEGER,  -- days since 2000-01-01
>> msec INTEGER,   -- milliseconds of day, just in case we ever
>have to
>> deal with subseconds
>> snr REAL,
>> elevation REAL,
>> abc REAL,
>> def REAL,
>> PRIMARY KEY(sat, day2000, msec)
>> );
>>
>> To find satellites and timestamps with SNR>30:
>>
>> SELECT sat, datetime('2000-01-01', day2000||' days', (msec/1000)||'
>> seconds') FROM satlog WHERE snr>30;
>>
>> I'm using similar as this,with now more than  4 years of 1 s
>satellite
>> data. Sqlite performs very well with a schema like this.
>>
>> (Your stream must of course be able to encode the leap seconds,
>otherwise
>> you cannot do anything on the Sqlite level. The GNSS satellites
>will send
>> data strictly at every s).
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-09 Thread Wout Mertens
So the satellites don't get leapsecond updates? I don't understand why it
can't just be an epoch…

On Sat, Jun 9, 2018, 8:39 AM Stephan Buchert  wrote:

> To take care of the leap seconds every ~1.5 years or so, you need a day
> segmented time stamp and a three column primary key:
>
> CREATE TABLE satlog (
> sat INTEGER,
> day2000 INTEGER,  -- days since 2000-01-01
> msec INTEGER,   -- milliseconds of day, just in case we ever have to
> deal with subseconds
> snr REAL,
> elevation REAL,
> abc REAL,
> def REAL,
> PRIMARY KEY(sat, day2000, msec)
> );
>
> To find satellites and timestamps with SNR>30:
>
> SELECT sat, datetime('2000-01-01', day2000||' days', (msec/1000)||'
> seconds') FROM satlog WHERE snr>30;
>
> I'm using similar as this,with now more than  4 years of 1 s satellite
> data. Sqlite performs very well with a schema like this.
>
> (Your stream must of course be able to encode the leap seconds, otherwise
> you cannot do anything on the Sqlite level. The GNSS satellites will send
> data strictly at every s).
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Selecting multiple similar columnname.

2018-06-09 Thread Stephan Buchert
To take care of the leap seconds every ~1.5 years or so, you need a day
segmented time stamp and a three column primary key:

CREATE TABLE satlog (
sat INTEGER,
day2000 INTEGER,  -- days since 2000-01-01
msec INTEGER,   -- milliseconds of day, just in case we ever have to
deal with subseconds
snr REAL,
elevation REAL,
abc REAL,
def REAL,
PRIMARY KEY(sat, day2000, msec)
);

To find satellites and timestamps with SNR>30:

SELECT sat, datetime('2000-01-01', day2000||' days', (msec/1000)||'
seconds') FROM satlog WHERE snr>30;

I'm using similar as this,with now more than  4 years of 1 s satellite
data. Sqlite performs very well with a schema like this.

(Your stream must of course be able to encode the leap seconds, otherwise
you cannot do anything on the Sqlite level. The GNSS satellites will send
data strictly at every s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-08 Thread R Smith
I forgot to add, if you do change the schema as advised, the query you 
wanted will become the very easy:


SELECT SatNum FROM SatLog WHERE SNR > 30;

Which will return one or more satellites as rows, or you can do as 
Gunter suggested:


SELECT group_concat(SatNum, '; ') FROM SatLog WHERE SNR > 30;

which will produce a single return value string with all the satellite 
numbers separated by semi-colons (or any other separator you would like).



On 2018/06/08 12:52 PM, Mukesh Kumar wrote:

Hi Ryan,

Thanks for the advice.
I cant share the exact Schema, however i can give an idea.

I am trying to store GNSS satellite info, which is streaming every second.
My Primary Key is the Timestamp at which i have got the information.. And
other columns in the table are the satellite information like
SNR,elevation,abc,def,ghi.. for each of the satellite.
max number of satellite at a given timestamp can be 64.
So in my schema, all the satellite parameters are getting repeated 64 times.

I want a easy way to extract the data, lets say get timestamp when any of
the satellite has SNR > 30.

My Idea was to write a simple query like

Select SNR* from Table where SNR*> 30;
where * takes value from 1-64.

Could you please suggest how to handle such scenarios ?

Regards
Mukesh




It's hard to suggest a way to do what you want given the schema you 
have, BUT, it's easy to achieve that if you can change the schema.


Why not have the table store the values for each satellite in a new row?
 So in stead of having:

TimeStamp-Sat1  |  SNR-Sat1  |  elevation-Sat1  | abc-Sat1  | etc. |  
etc...


Go for:

SatNumber  |  Timestamp  |  SNR  |  elevation  |  abc  |  etc

Now the obvious next question is, how to deal with the fact that the 
Timestamp is no longer unique? Well, that can be fixed by declaring a 
multiple-column Primary key, since the timestamp will still be unique 
per satellite, and the satellite number becomes a query-able entity - 
making for a schema like this:


CREATE TABLE SatLog(
  SatNum INTEGER NOT NULL,
  TimeStamp NUMERIC NOT NULL,
  SNR  REAL,
  elevation REAL,
  abc REAL,
  etc.,
  PRIMARY KEY (SatNum, TimeStamp)
);

Your insert syntax will be very much easier, and all queries will be 
easier. since after this change you can query individual satellites 
easily, getting satellite-specific stats using the powerful aggregate 
querying in sqlite.


Also, if later you drop or add new satellites, this schema will handle 
it painlessly, whereas the other one will require a re-make every time.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-08 Thread R Smith


On 2018/06/08 12:52 PM, Mukesh Kumar wrote:

Hi Ryan,

Thanks for the advice.
I cant share the exact Schema, however i can give an idea.

I am trying to store GNSS satellite info, which is streaming every second.
My Primary Key is the Timestamp at which i have got the information.. And
other columns in the table are the satellite information like
SNR,elevation,abc,def,ghi.. for each of the satellite.
max number of satellite at a given timestamp can be 64.
So in my schema, all the satellite parameters are getting repeated 64 times.

I want a easy way to extract the data, lets say get timestamp when any of
the satellite has SNR > 30.

My Idea was to write a simple query like

Select SNR* from Table where SNR*> 30;
where * takes value from 1-64.

Could you please suggest how to handle such scenarios ?


It's hard to suggest a way to do what you want given the schema you 
have, BUT, it's easy to achieve that if you can change the schema.


Why not have the table store the values for each satellite in a new row?
 So in stead of having:

TimeStamp-Sat1  |  SNR-Sat1  |  elevation-Sat1  | abc-Sat1  | etc. |  etc...

Go for:

SatNumber  |  Timestamp  |  SNR  |  elevation  |  abc  |  etc

Now the obvious next question is, how to deal with the fact that the 
Timestamp is no longer unique? Well, that can be fixed by declaring a 
multiple-column Primary key, since the timestamp will still be unique 
per satellite, and the satellite number becomes a query-able entity - 
making for a schema like this:


CREATE TABLE SatLog(
  SatNum INTEGER NOT NULL,
  TimeStamp NUMERIC NOT NULL,
  SNR  REAL,
  elevation REAL,
  abc REAL,
  etc.,
  PRIMARY KEY (SatNum, TimeStamp)
);

Your insert syntax will be very much easier, and all queries will be 
easier. since after this change you can query individual satellites 
easily, getting satellite-specific stats using the powerful aggregate 
querying in sqlite.


Also, if later you drop or add new satellites, this schema will handle 
it painlessly, whereas the other one will require a re-make every time.


Hope that helps, but please ask if anything is unclear or you have any 
more questions.

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-08 Thread Thomas Kurz
Why don't you store the values in a different table consisting only of the 
fields "satellite id/number", and "strength", and a link to the appropriate row 
in the source table (the one that contains the timestamp) ?

- Original Message - 
From: Mukesh Kumar 
To: SQLite mailing list 
Sent: Friday, June 8, 2018, 12:52:58
Subject: [sqlite] Selecting multiple similar columnname.

Hi Ryan,

Thanks for the advice.
I cant share the exact Schema, however i can give an idea.

I am trying to store GNSS satellite info, which is streaming every second.
My Primary Key is the Timestamp at which i have got the information.. And
other columns in the table are the satellite information like
SNR,elevation,abc,def,ghi.. for each of the satellite.
max number of satellite at a given timestamp can be 64.
So in my schema, all the satellite parameters are getting repeated 64 times.

I want a easy way to extract the data, lets say get timestamp when any of
the satellite has SNR > 30.

My Idea was to write a simple query like

Select SNR* from Table where SNR*> 30;
where * takes value from 1-64.

Could you please suggest how to handle such scenarios ?

Regards
Mukesh




P* Please don't print this e-mail unless you really need to.*

On Fri, Jun 8, 2018 at 3:35 PM, R Smith  wrote:


> On 2018/06/07 6:11 PM, mukeshkb4u wrote:

>> Hi All,
>> I have a table with multiple similar column names, like
>> abc_1,abc_2,abc_3...

>> Is there a way i can do a select on only these columns in a table, without
>> specifiying the full column list?
>> Can i use  a regular expression in selecting column names ?


> There is no such way, and shouldn't be. You could achieve this by writing
> a pre-processor on your sql queries, but honestly my advice for simplest
> solution - make a view showing only the specific set of columns and then
> query that view when needed - perhaps even joining that view to the
> original table if you wish to add one or two other columns.

> That said, I would like to reiterate Simon's point - If you have that many
> similar columns, it suggests a schema with scope to improve.  If you post
> the whole schema and its purpose/description, someone here might be able to
> suggest an easier/better way to do it.

> Cheers!
> Ryan

> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-08 Thread Mukesh Kumar
Hi Ryan,

Thanks for the advice.
I cant share the exact Schema, however i can give an idea.

I am trying to store GNSS satellite info, which is streaming every second.
My Primary Key is the Timestamp at which i have got the information.. And
other columns in the table are the satellite information like
SNR,elevation,abc,def,ghi.. for each of the satellite.
max number of satellite at a given timestamp can be 64.
So in my schema, all the satellite parameters are getting repeated 64 times.

I want a easy way to extract the data, lets say get timestamp when any of
the satellite has SNR > 30.

My Idea was to write a simple query like

Select SNR* from Table where SNR*> 30;
where * takes value from 1-64.

Could you please suggest how to handle such scenarios ?

Regards
Mukesh




P* Please don't print this e-mail unless you really need to.*

On Fri, Jun 8, 2018 at 3:35 PM, R Smith  wrote:

>
> On 2018/06/07 6:11 PM, mukeshkb4u wrote:
>
>> Hi All,
>> I have a table with multiple similar column names, like
>> abc_1,abc_2,abc_3...
>>
>> Is there a way i can do a select on only these columns in a table, without
>> specifiying the full column list?
>> Can i use  a regular expression in selecting column names ?
>>
>
> There is no such way, and shouldn't be. You could achieve this by writing
> a pre-processor on your sql queries, but honestly my advice for simplest
> solution - make a view showing only the specific set of columns and then
> query that view when needed - perhaps even joining that view to the
> original table if you wish to add one or two other columns.
>
> That said, I would like to reiterate Simon's point - If you have that many
> similar columns, it suggests a schema with scope to improve.  If you post
> the whole schema and its purpose/description, someone here might be able to
> suggest an easier/better way to do it.
>
> Cheers!
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-08 Thread R Smith


On 2018/06/07 6:11 PM, mukeshkb4u wrote:

Hi All,
I have a table with multiple similar column names, like
abc_1,abc_2,abc_3...

Is there a way i can do a select on only these columns in a table, without
specifiying the full column list?
Can i use  a regular expression in selecting column names ?


There is no such way, and shouldn't be. You could achieve this by 
writing a pre-processor on your sql queries, but honestly my advice for 
simplest solution - make a view showing only the specific set of columns 
and then query that view when needed - perhaps even joining that view to 
the original table if you wish to add one or two other columns.


That said, I would like to reiterate Simon's point - If you have that 
many similar columns, it suggests a schema with scope to improve.  If 
you post the whole schema and its purpose/description, someone here 
might be able to suggest an easier/better way to do it.


Cheers!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Selecting multiple similar columnname.

2018-06-07 Thread Simon Slavin
On 7 Jun 2018, at 5:11pm, mukeshkb4u  wrote:

> I have a table with multiple similar column names, like
> abc_1,abc_2,abc_3...
> 
> Is there a way i can do a select on only these columns in a table, without
> specifiying the full column list?
> Can i use  a regular expression in selecting column names ?

No.  You could use one PRAGMA call to find out the column names of that format, 
then use the result to build your query string.  But there's no way to do it 
directly in a single statement.

However, the use of numbered column names is often a sign that your schema 
needs rethinking.  Rather than these being multiple columns in a row, could 
these not be separate rows in a table ?  Then you could have a single SELECT, 
perhaps using group_concat(), retrieve just the values you want.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Selecting multiple similar columnname.

2018-06-07 Thread mukeshkb4u
Hi All,
I have a table with multiple similar column names, like
abc_1,abc_2,abc_3...

Is there a way i can do a select on only these columns in a table, without
specifiying the full column list?
Can i use  a regular expression in selecting column names ?

Regards
Mukesh



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users