CODEWITHSUNDEEP
sqlsql-server-2008t-sqldatetime
apsillers
apsillers

Reputation: 115950

Storing a time difference in SQL

In my application, I have a database storing a calendar of events:

 id |        name        |        date
----+--------------------+--------------------
  1 | Birthday Party     | 2013-04-27 16:30:00
  2 | Dinner Reservation | 2013-03-20 17:00:00
  3 | Sunday Brunch      | 2013-03-31 11:15:00

When viewing events in the application, users should be able to configure how far in advance from the present moment they wish to view events, stored as a value in the database:

 username |     datediff
----------+------------------------------------------
  user123 |  2 days in advance
  goodguy |  93 days in advance
  spudly  |  365 days in advance
  aaaaaa  |  17 days, 3 hours, 30 seconds in advance

My question is: what is the best (i.e., most SQL-idiomatic) way to store such a date differential? I could store the time difference as a number in milliseconds, but is there some built-in SQL datatype that is suitable for date differentials, rather than just particular points in time? Is something like DATETIME or TIMESTAMP appropriate for this task?

It must be a relative difference -- for example, for "2 days in advance" I'm not interested in storing a particular date two days in the future, because I'd like the user to see events for the next two days every time he looks at the application.

I'm using Microsoft SQL Server 2008, if it makes any difference.

(This may be a duplicate, but all my search attempts have turned up results about datediff -- which is used to calculate time differences -- but nothing about how best to store time differences.)

Upvotes: 1

Views: 3100

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

Standard SQL has a specific data type for date and time durations: interval. SQL Server doesn't support the interval data type.

DateDiff() returns a signed integer. If you need to store the SQL Server equivalent to a SQL interval, you'll need to store an integer. The integer is a count of the number of datepart boundaries, so you also need to store what kind of datepart boundary the integer refers to. Without the datepart, the signed integer 3 could just as easily mean 3 years or 3 seconds.

As a practical matter, I think I'd rather calculate a timestamp for the reminder, and store that instead of the integer and datepart that define an interval. A timestamp can be indexed and queried much more simply than the integer and datepart. And without the need to support recurring events, I don't see a compelling reason to build a solution more complicated than that.

Upvotes: 1

Related Questions