2

How can consecutive periods of time (defined by a start and end date) be stored in a database? For example:

  • from 2018-01-01 00:00:00+01:00 until 2018-06-01 00:00:00+02:00
  • from 2018-06-01 00:00:00+02:00 until 2018-10-01 00:00:00+02:00
  • from 2018-10-01 00:00:00+02:00 onwards (meaning 'indefinitely')

The important factor being that the end date of one timespan always needs to match the start date of another.

Main concerns:

  • Storing the same value (end date of one, start date of the next) twice sounds suboptimal. Constraints would be a necessity to avoid irregularities.
  • Not storing the end date and using the start date of a different record/row makes each individual row hard to reason about. Querying gets harder.

Both concerns make me wonder if there is a better approach?

3
  • 2
    what do you want to happen if an attempt to store a non-consecutive period is made? – Ewan Sep 25 '18 at 10:29
  • Save only start date. The next higher timestamp can then be considered as the end of previous interval. – S.D. Sep 25 '18 at 12:03
  • There are database products that are specifically optimized for storing time sequences. As well as optimized storage, they also provide query mechanisms that may be better for handling them (eg ability to perform merging operations on 'n' sequential periods without needing to know when the appropriate start and end times are ... This requires n-1 index lookups in a traditional relational database but can be optimized to not require any at all). – Jules Sep 25 '18 at 14:47
10

Not storing the end date and using the start date of a different record/row make each individual row hard to reason about. Querying gets harder.

SQL Server 2012 introduced LEAD and LAG to help with these kinds of queries. Full explanation here.

A short example for your case:

SELECT 
    StartDate,        
    LEAD(StartDate) OVER (ORDER BY StartDate) as EndDate
FROM
    MyTable

This makes it possible for you to store only the start date in the database table, but then create a view which returns a table that contains both the (stored) startdate and (calculated) end date.

This gives you the best of both worlds. Internally, you ensure data integrity. Externally, you have a view that's easier to process/query further.


Overall, your assesment is correct. Storing the end/start date twice is easier (at the cost of data integrity), whereas removing the duplicate values is gives you better data integrity (but takes more effort to implement.

This type of problem is often a "pick your poison" type of deal. For example, consider the example of storing the folder/file sizes in a directory system. This makes the problem outset a bit more blatant, due to the recursive nature of directory structures.

  • Do you store a size for every folder separately? Then you only have to query the folder to find its size, but it's possible that you store the data in a way that a parent directory's size does not equal the total size of its content (= fast querying; but open to data inconsistency)
  • Do you calculate a top-level directory's size based on its contents? Then you're guaranteed to get the right answer, but you have to recursively calculate the size of the folder's contents every time you want to know a folder's size (= guaranteed data consistency; but slow querying)

But that's a choice you're going to have to make. What are your priorities? Speed of development? Or quality/consistency? There is no one true answer.

2
  • Brilliant! Worth mentioning: storing start and end is also more robust: how many time did I here about strictly sequential periods only to discover later that there were exceptions with gaps and overlaps ;-) – Christophe Sep 25 '18 at 18:12
  • @Christophe To be fair, if the customer asks for something different than they actually need, that's not my problem. I don't mind involving the customer in the development process but I can only build the project according to the specs they give me. – Flater Sep 25 '18 at 20:18
0

One possibility is to normalize the dates into a separate table. Create a table with a sequence (autonumber or whatever your database uses) as the primary key, and the date/time as the second column. Then the relation between the original table and the date/time table will simply involve storing the date/time primary key in both fields. For example, if your first interval is from 2001-01-01T12:30:00z to 2004-01-02T12:30:01z and your second is from 2004-01-02T12:30:01z to 2006-02-02T11:30:01z, you store the three dates in the date table:

ID Date 
 1  2001-01-01T12:30:00z 
 2  2004-01-02T12:30:01z
 3  2006-02-02T11:30:01z

Then in the original event table you reference the date table

Name    Start_ID End_ID
 BEFORE  1        2   
 DURING  2        3
 AFTER   3        null 

The AFTER is an example if you want to leave a date interval open to present, and maybe modify it later when the AFTER period ends.

0

Alex Kuznetsov has published a schema and algorithm for just situation. It involves adding another column (PreviousFinishedAt) and constraints. It's tricky to get your head around, but it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.