CODEWITHSUNDEEP
mysqldatetime
ryaan_anthony
ryaan_anthony

Reputation: 151

SQL: Select the next datetime in the future from one of two columns

I have a table similar to this:

+---------------------+---------------------+
| from                | to                  |
+---------------------+---------------------+
| 2014-07-01 01:00:00 | NULL                |
+---------------------+---------------------+
| 2015-08-01 02:00:00 | 2015-10-01 02:00:00 |
+---------------------+---------------------+
| 2015-09-01 03:00:00 | 2015-10-01 03:00:00 |
+---------------------+---------------------+

And i need to know the next datetime in the future, which should be: 2015-08-01 02:00:00

i'm looking for the "correct" answer, ideally without subqueries or joins.

Upvotes: 2

Views: 1345

Answers (4)

Alan
Alan

Reputation: 39

You can get the next closest date in each column by using

SELECT MIN(from) AS MinFrom, MIN(to) as MinTo FROM TABLENAME

Getting the smallest of both is a little harder and requires nesting, but it's doable.

SELECT MIN(COM.Dates) FROM (
    SELECT from AS Dates FROM TABLENAME
    UNION
    SELECT to FROM TABLENAME
) AS COM

EDIT: Realized that only one overarching MIN was needed.

Upvotes: 0

kojiro
kojiro

Reputation: 77089

Edit:

Assuming that from is always before to for each row, you don't even need LEAST:

SELECT MIN(IF(`from` > NOW(), `from`, `to`))
FROM `yourtable`
WHERE `from` > NOW()
   OR `to` > NOW();

This works because one of from or to have to be > NOW() or the WHERE eliminates that row. So in procedural pseudo code this is like

x = ("from" where "from" is > now) and
    ("to" where "from" is < now and "to" > now)
min(x)

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

I would start by writing a query to get all rows with a null to column, as well as a query that gets the minimum date from each row, and then join them together on the condition that the minimum row date is later than the date in the first table:

SELECT m.*, t.dateCol
FROM myTable m
JOIN(
  SELECT LEAST(fromCol, toCol) AS dateCol
  FROM myTable) t ON t.dateCol > m.fromCol AND m.toCol IS NULL;

Once you have that, you can group by fromCol and get the minimum dateCol as the next date:

SELECT m.fromCol, MIN(t.dateCol) AS nextDate
FROM myTable m
JOIN(
  SELECT LEAST(fromCol, toCol) AS dateCol
  FROM myTable) t ON t.dateCol > m.fromCol AND m.toCol IS NULL
GROUP BY m.fromCol;

The way that would look in an update is something like this:

UPDATE myTable m
JOIN(
   SELECT m.fromCol, MIN(t.dateCol) AS nextDate
   FROM myTable m
   JOIN(
      SELECT LEAST(fromCol, toCol) AS dateCol
      FROM myTable) t ON t.dateCol > m.fromCol AND m.toCol IS NULL
   GROUP BY m.fromCol) t ON t.fromCol = m.fromCol
SET m.toCol = t.nextDate;

Here is an SQL Fiddle example.

Upvotes: 0

NielsB95
NielsB95

Reputation: 32

Is there always a certain amount of time between those two dates? When it is always the same it shouldn't be to hard, because you can use something like this:

Update 'table'
Set to = from + INTERVAL 1 DAY

Obviously you can change the number of days that fits for your query.

Upvotes: 0

Related Questions