Multiply and divide dates

datedate mathoracle

I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line.

It was centred around a road_condition table:

COND_ID ROAD_ID   COND_DATE  CONDITION
------- -------   --------   ---------
      1     100   84-11-01          18
      2     100   09-01-01           6
      3     100   12-06-19           4
      4     100   15-04-29           4

      5     200   92-04-29          20
      6     200   17-04-05           3

Note: I consider cond_date to be the X-axis, and condition to be the Y-axis.

In my an attempt to answer my own question, I tried to do things like use cond_date as X1 in a calculation:

y1 - ((y2-y1)/(x2-x1)) * x1 as y_intercept

However, obviously I can't just use a date in a calculation that involves multiplication or division; dates are not traditional numbers.

As a work around, I stripped the date of its month and day: extract(year from cond_date) and performed the calculations from there on the resulting integer (the year). However, by doing this, I of course lose valuable precision in the number. This compromises the output of the calculation.

How can I multiply and divide dates, without losing precision?

Best Answer

I think you can convert each date value to a number of time units -- days, hours, seconds etc. according to your precision requirement -- since some base date, similar to the Unix epoch. This will allow you to manipulate them as regular numbers.

For example, the expression cond_date - TO_DATE('1970-01-01', 'YYYY-MM-DD') will give you the number of days since 1 Jan. 1970.

Related Solutions

How to compare dates in SQL

Depending on what Database you are using, you should be able to cast the string to a date and then do your comparison.

i.e. in Sql Server

cast(date as datetime) >= '20150131' ...

That said, I'd typically recommend storing the date as a date if at all possible, and if you do need to use strings, It'd be best to use a more standardized format

i.e. 'YYYY-mm-dd' or even better 'YYYYmmdd' instead of 'mm/dd/YYYY' which, for example, can easily be mixed up with 'dd/mm/YYYY' which is common in many cultures. See this blog post: Bad habits to kick : mis-handling date / range queries

Mysql – Search between dates where all dates appear

I believe you want to get house_id where date exists for each day between @startDate and @finalData.

Sample queries with 3 different house_id: SQL Fiddle.

Query:

SET @startDate := CAST('2016-02-01' as date);
SET @finalDate := CAST('2016-02-04' as date);

SELECT house_id
    , COUNT(*)
    , DATEDIFF(@startDate, @finalDate)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id;

Query 1 in SQL Fiddle.

This query GROUP BY house_id and output the COUNT of days between @startDate and @finalData:

house_id    COUNT(*)    DATEDIFF(@startDate, @finalDate)
1           3                       3
2           2                       3
3           3                       3

From this query, it is obvious that only rows where COUNT(*) = DATEDIFF(@startDate, @finalDate) should be returned.

If (house_id, date) is not unique, COUNT(*) must be replaced by COUNT(DISTINCT `date`).

Query with all days

If you only want house_id where COUNT(*) = DATEDIFF(day, @startDate, @finalDate), the query must use the HAVING clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 2 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3
3           3

Query where `status = X`

Status value can be checked in the WHERE clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
    AND status = '1'
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 3 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3

Best Answer

Related Solutions

How to compare dates in SQL

Mysql – Search between dates where all dates appear

Query:

Query with all days

Output:

Query where status = X

Output:

Related Question

Query where `status = X`