How to get rid of the end date field

Question

I have a table where I have the following fields:

start | end | field A | field B | field C | id

I want to get rid of the end field.

In my application, the user provides a single date, and I find the data for the entry that has that date in between start and end. I want to make it so I just make it that I find the entry that is after the start date and before the next start date.

How do I put together that SQL query??

Right now its:

Select * from table where $date between start and end

I imagine I have to do a join of that table on itself

If you want to get rid of the start date, how/why are you still planning on querying against it? — Dave, Sep 01 '13 at 20:18
@Dave, I meant I want to get rid of the end date, but wrote start instead. Anyway I fixed it, thanks for pointing it out! — SimaPro, Sep 01 '13 at 20:27

Gordon Linoff · Accepted Answer · 2013-09-01T20:31:09.373

0

You can do this with:

Select *
from table
where $date >= start
order by start
limit 1;

EDIT:

If you want the first record after the end date, then you would do something similar, just with the comparison on end instead of start:

Select *
from table
where $date > end
order by start
limit 1;

edited Sep 01 '13 at 20:31

answered Sep 01 '13 at 20:18

Gordon Linoff

1,242,037
58
646
786

This will sort all rows with start <= $start just to return one row. But I cannot think of a more efficient solution without this redundancy. – jboi Sep 01 '13 at 20:23
@jboi . . . It will only sort the rows if there is no index on `start`. – Gordon Linoff Sep 01 '13 at 20:25
Hmm, I see. I had thought it would involve some sort of join. Now I have a new question, I guess, where I do want the end date, but I want to pull it from the following entry's start date...any ideas? – SimaPro Sep 01 '13 at 20:28
1

@SimaPro - Does your table have a `unique key`? Anyway, have a look at answers to [this SO question](http://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql). – PM 77-1 Sep 01 '13 at 20:37
I'm not just favoring you, Gordon :P, this answers the question so I'm accepting it as the answer. Thanks! – SimaPro Sep 01 '13 at 21:12
@PM77-1 the unique key is the id. I can't use start as the unique key because of the nature of the application which I simplified here, but that was a good link, thanks for that – SimaPro Sep 01 '13 at 21:13
@Gordon Linoff. An index would help if it is composed if the join fields and the start date ascending. How would the full replacement of the between look like? – jboi Sep 01 '13 at 21:52

score 0 · Answer 2 · answered Sep 01 '13 at 20:42

In fact in this case you want to keep the start and the end date. Even if it is redundant information.

In Data Warehouses we have the entities of slowly changing dimensions. They're build by chaining start and end dates - just like in your example. Keeping this particular redundancy is way more efficient then searching with each query for the very next entry. It is possible but it means to run thru sub queries or sort a part of the table.

How do the professional analytical databases handle this?

IBM and Teradata have a feature called temporal. It contains one data type (PERIOD) that contains the start and the end date with additional functions to handle overlaps and the like.

Bottom line: keep the redundancy and enjoy the short answer times.

How to get rid of the end date field

2 Answers2