mardi 4 août 2015

Alternative to strtotime() in MySQL

I have an EventSet table with those fields:

  • start_day (ex. Monday)
  • start_time (ex. 14:20:00)

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.

Aucun commentaire:

Enregistrer un commentaire