Menu
I have a question: Is it possible to select from a MySQL database by comparing one DATE string "2010-04-29" against strings that are stored as DATETIME (2010-04-29 10:00)?
I have one date picker that filters data and I would like to query the table by the DATETIME field like this:
SELECT * FROM `calendar` WHERE startTime = '2010-04-29'" ...and I would like to get the row that has the DATETIME value of "2010-04-29 10:00".
Any suggestions? Thanks.
999
The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".
STRCMP() function in MySQL is used to compare two strings. If both of the strings are same then it returns 0, if the first argument is smaller than the second according to the defined order it returns -1 and it returns 1 when the second one is smaller the first one.
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR . To get the difference in seconds as we have done here, choose SECOND .
Use the following:
SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29' Just for reference I have a 2 million record table, I ran a similar query. Salils answer took 4.48 seconds, the above took 2.25 seconds.
So if the table is BIG I would suggest this rather.
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
