Databases
StackOverflow2013
Postsdbo
PODetermine 'valid until' date in MySQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODetermine 'valid until' date in MySQL query
    primarykey
    data
    text
    <p>In this table I store changeable values for certain spids. As days go by, these values can change, and an spid can get a new value.</p> <p>Now I want to find out until when a certain value is enabled.</p> <p>This is the data for a single spids</p> <pre><code>uid spid propertyvalue creationdate -------------------------------------------------- 1 3 First name 2010-01-17 22:34:00 37 3 Second name 2010-01-18 01:07:24 38 3 Third name 2010-01-18 01:09:00 39 3 Fourth name 2010-01-18 01:18:16 40 3 Fifth name 2010-01-18 01:20:02 </code></pre> <p>So the first value starts at <strong>2010-01-18 01:07:24</strong> and would be valid until <strong>2010-01-18 01:01:00</strong>.</p> <p>I already tried this query:</p> <pre><code>SELECT s1 . * , s2.creationdate AS datetwo FROM salespointproperty s1 JOIN salespointproperty s2 ON s1.spid = s2.spid AND s1.creationdate &lt; s2.creationdate </code></pre> <p>That gives me a lot of double-records (some of them wrong), and it always leaves out the last new name (as it doesn't have a new creationdate following it) Example:</p> <pre><code>uid spid propertyvalue creationdate datetwo -------------------------------------------------------------- 1 3 First name 2010-01-17 22:34:00 2010-01-18 01:07:24 * 1 3 First name 2010-01-17 22:34:00 2010-01-18 01:09:00 37 3 Second name 2010-01-18 01:07:24 2010-01-18 01:09:00 * 1 3 First name 2010-01-17 22:34:00 2010-01-18 01:18:16 37 3 Second name 2010-01-18 01:07:24 2010-01-18 01:18:16 38 3 Third name 2010-01-18 01:09:00 2010-01-18 01:18:16 * 1 3 First name 2010-01-17 22:34:00 2010-01-18 01:20:02 37 3 Second name 2010-01-18 01:07:24 2010-01-18 01:20:02 38 3 Third name 2010-01-18 01:09:00 2010-01-18 01:20:02 39 3 Fourth name 2010-01-18 01:18:16 2010-01-18 01:20:02 * </code></pre> <p>Only the lines with an asterix are correct. The fifth name is missing.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USskerit
    1. USskerit
    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. This table or related slice is empty.
    1. COThat join doesn't make a lot of sense.
      singulars
      1. PODetermine 'valid until' date in MySQL query
      1. USAlix Axel
    2. CO1. Your sample data doesn't have uid = 1. Please add that in. 2. In the results, the creationdates of uid 37-39 are the creationdates of 36-38 respectively. Is that right?
      singulars
      1. PODetermine 'valid until' date in MySQL query
      1. USlins314159
    3. COYes they were, I fixed them now.
      singulars
      1. PODetermine 'valid until' date in MySQL query
      1. USskerit
 

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