Matching Dates in Database For Calendar

cgdtalent · 01-10-2013, 04:39 PM

I'm creating a very basic calendar where each day's event's are loaded in from the database. My original thought was just to compare the current day of the calendar to the day in the database, and just take all that info and populate that day. But then I realized that the dates in the database are to be stored as a TIMESTAMP (2013-01-01 00:00:01) so if I take the current day when the calendar is being populated in this format 2013-01-01 and try to match it to dates in the database in the timestamp format, that won't match up...right? Is there a way around this problem so it'll only match up the day not the actual time in hours?

TFlan · 01-10-2013, 05:35 PM

wrong

cgdtalent · 01-10-2013, 06:00 PM

Ok thanks...that makes a lot of sense.

Old Pedant · 01-10-2013, 07:23 PM

*** WRONG ANSWER *** (or at least not a very efficient or simple answer)

MUCH easier:

Code:

SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = '2013-1-13'

Or, if you want to match to *today*, it is even simpler:

Code:

SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = CURDATE()

The DATE( ) function in MySQL extracts the data alone from a date and time field.

So, for example, it converts '2013-1-22 17:13:12' to just '2013-1-22' for you.

cgdtalent · 01-10-2013, 07:29 PM

Quote:

Originally Posted by Old Pedant

*** WRONG ANSWER *** (or at least not a very efficient or simple answer)

MUCH easier:

Code:

SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = '2013-1-13'

Or, if you want to match to *today*, it is even simpler:

Code:

SELECT * FROM yourtable WHERE DATE(yourtimestampfield) = CURDATE()

The DATE( ) function in MySQL extracts the data alone from a date and time field.

So, for example, it converts '2013-1-22 17:13:12' to just '2013-1-22' for you.

Oh ok...this is great! Thanks for clarifying that!

Old Pedant · 01-10-2013, 07:51 PM

By the by, when comparing dates and times, MySQL does *NOT* require that you use zero padding on months and days (and hours/minutes/seconds) that are single digits.

That is, it sees '2013-1-7' as the same as '2013-01-07'. MySQL does *NOT* actually store dates and times in that format (as strings). It always converts the string formats to the internal format before doing calculations, comparisons, etc., or before storing a value in the DB>