-1

I have a table in database for meeting schedules. It has 2 columns named start and end and since I dont have access to the php script which fills this table with new data, I am not sure in which format it is.

But PHPMyAdmin shows taht the columns for start and end are varchar(15) So I guess it should be datetime compatible.

Example in DB: 1378033200 Which shows as 01 September 2013

My question is, I want to pull the meetings and show them in a html page, but I do not want meetings which are older than 2 days ago (server time) to show up. What will be the query?

SELECT * FROM schedules ORDER BY start

Something like

SELECT * FROM schedules ORDER BY start WHERE start > 2 days ago

I tried this but it seems it does nothing!

SELECT *
FROM schedules 
WHERE COALESCE(start, 0) < CURDATE() - INTERVAL 2 DAY 
ORDER BY start
Dumbo
  • 13,555
  • 54
  • 184
  • 288

2 Answers2

1

But PHPMyAdmin shows taht the columns for start and end are varchar(15) So I guess it should be datetime compatible.

You've guessed wrong. Strings are only sortable as strings. Which means, unless you're using a sortable date formats (YYYY/MM/DD being one: I'm not aware of others) you'll have to parse all the results and do the calculation by yourself in PHP (otherwise, 13/11/2000 will come before 14/01/2000). Alternatively, you might wanna use the proper type for your column: datetime, date or timestamp. Once you'll do that, you'll be able to query your db and compare dates with < and > operators.

For the 2 days ago part, you'd like to know that MySql has a built in NOW variable to which you can sum/subtract days. If you'll design your db correctly, you won't even have to touch PHP (which a desiderable thing).

Community
  • 1
  • 1
Saturnix
  • 10,130
  • 17
  • 64
  • 120
1

Try this:

SELECT *
FROM schedules 
WHERE COALESCE(start, 0) < DATE_SUB(NOW(), INTERVAL 2 DAYS)
ORDER BY start