CODEWITHSUNDEEP
phpmysqldatetime
Nat Naydenova
Nat Naydenova

Reputation: 789

Alternative to strtotime() in MySQL

I have an EventSet table with those fields:

I want to create a query that selects specific records by comparing their day an time with the current ones.

So, using a little help from the PHP's strtotime(); method I am aiming at something similar to: 

SELECT * 
FROM 
   `EventSet` 
WHERE 
    DATE_FORMAT(NOW(), '%W %H:%i:%s') <= 
    date('%W %H:%i:%s', strtotime('start_day start_time'))

The last date(); part is of course pure PHP


So far I've tried using DATE_FORMAT with CONCAT_WS and also TIMESTAMP(); / UNIX_TIMESTAMP(); but am clearly messing something up. 

SELECT * 
FROM 
    `EventSet` 
WHERE 
    DATE_FORMAT(NOW(), '%W %H:%i:%s') <= 
    DATE_FORMAT(CONCAT_WS(' ', start_day, start_time), '%W %H:%i:%s')

I've looked through many similar questions here and in the net, but they all already have a date field to work with and there's no need to concatenate anything, so I can't use any of them.

Upvotes: 2

Views: 4632

Answers (2)

Nat Naydenova
Nat Naydenova

Reputation: 789

Thanks to Gordon Linoff's and @spash58's solutions I've a working code now.

I'm however extending the query a bit in order to make the time dependent of the day:

$conn = $this->getEntityManager()->getConnection();

$todayObject = new \DateTime();
$weekDay = $todayObject->format('N') - 1; // 0 - Monday; 6 - Sunday

$sql = "
    SELECT 
        start_day,
        start_time
    FROM
        EventSet
    WHERE 
        is_active = 1 AND 
        (
            WEEKDAY(NOW()) < start_day OR
            ( 
                WEEKDAY(NOW()) = start_day AND 
                TIME(NOW()) <= TIME(start_time)
            )
        )

    ORDER BY start_day ASC
    LIMIT 1                                    
";

$st = $conn->prepare($sql);
$st->execute();

$data = $st->fetchAll();

The start_date field is now converted to INT instead of string to ease the comparing. If the current week day is less than the start_day I don't really care about the time. Otherwise I include it in the condition.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You are using <=. This is not going to work for the names of the days of the week, so split the logic into two parts: one for the day of the week and the other for the time:

SELECT e.* 
FROM `EventSet` e
WHERE DATE_FORMAT(NOW(), '%W') = $start_day AND
      DATE_FORMAT(NOW(), '%H:%i:%s') <= DATE_FORMAT($start_time, '%H:%i:%s')

Note: I left the date_format() in for the time comparisons, I prefer:

SELECT e.* 
FROM `EventSet` e
WHERE DATE_FORMAT(NOW(), '%W') = $start_day AND
      TIME(NOW())) <= TIME($start_time)

Upvotes: 1

Related Questions