Databases
StackOverflow2013
Postsdbo
POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
    primarykey
    data
    text
    <p>I am working with SQLite and Python and try to get these specific dates (and corresponding values). I think an example explainst best what I am looking for. Consider selecting from this data:</p> <pre><code>-(2011-01-02, 4.0) -(2011-01-14, 5.0) -(2011-02-02, 5.5) -(2011-02-07, 6.5) -(2011-02-12, 7.0) -(2011-02-15, 4.0) -(2011-03-02, 2.5) -(2011-03-09, 3.5) </code></pre> <p>with a period-distance of <strong>one month</strong> (or a multiple) between the first date and all the dates we are looking for we return this data:</p> <pre><code>-(2011-01-02, 4.0) -(2011-02-02, 5.5) -(2011-03-02, 2.5) </code></pre> <p>I was looking for an SQL-statement which provides me with this data, if that is possible.</p> <p><strong>Additional question:</strong> if there is no date-point which is exactly a multiple of a month away from the initial date, how can I get the nearest date in my database?</p> <p>I hope that my explanation is clear enough. If not, thanks for telling.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRandomtheories
    1. USRandomtheories
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMy gut tells me this just isn't possible with SQLite SQL. It might be possible using Postgres WITH RECURSIVE, but it would probably have the same complexity as doing it in a stored procedure. Since SQLite data is local, I'd probably just loop over the possible dates and do a separate query for each.
      singulars
      1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
      1. USmillimoose
    2. COThanks for your response. I try to figure out this problem all day. I was hoping that there is some kind of loop command in SQLite, but as far as I read, there isn't. An alternative I had in mind was pulling all the dates from the database `curs.execute('''SELECT date, value FROM stockPrices WHERE name= ? AND date BETWEEN ? AND ? ORDER BY date ASC;''', (name, start, end))` and then writing a function in Python which selects the specific dates. But I am still hoping for a more elegant way.
      singulars
      1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
      1. USRandomtheories
    3. COYou could pull the minimum date and the maximum date from the table, then in Python, loop over all the "candidate dates" in this range. For each of these, `select * from stockPrices where date between [candidate - period] and [candidate + period] order by abs(julianday(date)-julianday(?)) asc limit 1`. Depending on whether SQLite and the Python drivers allow this, you might also use a subquery and do something like `select ([subquery]) from values ([candidate1]),([candidate2])`, but that's probably a little out of scope for SQLite.
      singulars
      1. POSQLite: Select all date-value-pairs where the distance between each date and the first date is a multiple of a specific period
      1. USmillimoose
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload