CODEWITHSUNDEEP
phpmysqldate
Sharan Mohandas
Sharan Mohandas

Reputation: 871

Find Ongoing and Upcoming Events

I have programmed a system which allows administrator to add bacthes. (Basic details + Date of Start and End date). The date format is YYYY/MM/DD. Type : VARCHAR (50). Shoud I use different type (Need expert advice)

I need to list the upcoming and ongoing batches

This is the query which i think would return the ongoing projects

SELECT * FROM PROGRAMMES WHERE 'startdate' <= '$currentdate' AND 'enddate' > '$currentdate'

and for Upcoming projects

`SELECT * FROM PROGRAMMES WHERE 'startdate' >= '$currentdate`'

But I'm unsure about the results.. Since the value is stored in VARCHAR (Would it fetch my required results).. Should i use a different data type for date.. If so please specify

I couldn't understand much from PHP tutorials as everywhere its different.. Could someone guide me with the most commonly used method.

Thanks

Upvotes: 0

Views: 416

Answers (2)

Bogdan Burym
Bogdan Burym

Reputation: 5512

The method I use is the following:

  • always use DATETIME type for datetime.
  • always store dates in single timezone (UTC as base if project will need to show date in different TZ).

You can compare DATETIME columns using operators >, >=, <, <=, =. It is very easy to use and is definitely better then storing date as VARCHAR.

For example, you could find events which have finished last day like this:

... WHERE date_column < NOW() AND date_column > NOW() - INTERVAL 1 DAY

Also, this might help you Best practice for storing the date in MySQL from PHP

Upvotes: 2

Pudich Denis
Pudich Denis

Reputation: 11

Try this:

SELECT * FROM PROGRAMMES WHERE startdate <= $currentdate AND enddate > $currentdate

Upvotes: 0

Related Questions