Question 20436 in Mysql Question posted in
The official MySQL documentation can be found here.

Selecting Records Where Value in One Field is the Same As or Later than Value in Another Field – Mysql

I have a large table called uk that includes fields called CurrTime and EvName.

Using the following query (extract below) I create a new field called StartTime that extracts the time from EvName (which is a MEDIUMTEXT field). I want to now select only the records where CurrTime is the same as or later than StartTime.

I am new to MySQL and don’t even know where to start with this. Can anyone help?

SELECT
    *,
    CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', 1), ' ', -1), ":", SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', -1), ' ', 1)) AS StartTime
FROM uk;

Query result:

CurrTime EvName StartTime
2022-06-01 17:39:29 Corby 1st Jun – 17:45 17:45
2022-06-01 17:39:17 Corby 1st Jun – 17:45 17:45
2022-06-01 17:45:39 Corby 1st Jun – 17:45 17:45
2022-06-01 17:37:51 Corby 1st Jun – 17:45 17:45
2022-06-01 17:48:03 Corby 1st Jun – 17:45 17:45
2022-01-01 12:47:54 Froom 1st Jan – 12:55 12:55
2022-01-01 12:51:15 Froom 1st Jan – 12:55 12:55
2022-01-01 12:51:12 Froom 1st Jan – 12:55 12:55
2022-01-01 12:55:02 Froom 1st Jan – 12:55 12:55
2022-01-01 12:41:29 Froom 1st Jan – 12:55 12:55
2022-01-01 12:52:29 Froom 1st Jan – 12:55 12:55
2022-01-01 12:57:56 Froom 1st Jan – 12:55 12:55

Desired result:

CurrTime EvName StartTime
2022-06-01 17:45:39 Corby 1st Jun – 17:45 17:45
2022-06-01 17:48:03 Corby 1st Jun – 17:45 17:45
2022-01-01 12:55:02 Froom 1st Jan – 12:55 12:55
2022-01-01 12:57:56 Froom 1st Jan – 12:55 12:55
Tags:

3

Answers


  1. 0

    The easiest way is to use Time() function and compare.
    Probably this is what you are looking for:

    SELECT
    *
FROM uk
WHERE
   TIME(CurrTime) >= TIME(CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', 1), ' ', -1), ":", SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', -1), ' ', 1)))
    Login or Signup to reply.
  2. 0

    You are overcomplicating the extraction of StartTime.
    You can do it simply like:

    TRIM(SUBSTRING_INDEX(EvName, '-', -1))

    and the query that you want would be:

    SELECT *, TRIM(SUBSTRING_INDEX(EvName, '-', -1)) AS StartTime
FROM uk
WHERE TIME(CurrTime) >= TIME(TRIM(SUBSTRING_INDEX(EvName, '-', -1)));

    See the demo.

    Login or Signup to reply.
  3. 0

    You could use time() to get the expected data :

    With cte as (
  SELECT
    *,
    CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', 1), ' ', -1), ":", SUBSTRING_INDEX(SUBSTRING_INDEX(EvName, ':', -1), ' ', 1)) AS StartTime  
    FROM uk
)
select *
from cte
where TIME(CurrTime) > time(StartTime);

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search