Menu
I have the following entry in my DB table
eventName(varchar 100) -> myEvent date(timestamp) -> 2013-03-26 09:00:00 and I am trying to use the following query;
SELECT * FROM eventList WHERE `date` BETWEEN UNIX_TIMESTAMP(1364256001) AND UNIX_TIMESTAMP(1364342399) i.e between 2013-03-26 00:00:01 and 2013-03-26 23:59:59
but it is giving me 0 results.
I have tried expanding the date range with no luck and there are definitely results within the range.
any help is appreciated.
454
As stated above, the format of date and time in our table shall be yyyy:mm: dd hh:mm: ss which is implied by DATETIME2. The time is in a 24-hour format. Syntax: SELECT * FROM TABLE_NAME WHERE DATE_TIME_COLUMN BETWEEN 'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';
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 .
If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure .
Try:
SELECT * FROM eventList WHERE `date` BETWEEN FROM_UNIXTIME(1364256001) AND FROM_UNIXTIME(1364342399) Or
SELECT * FROM eventList WHERE `date` BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59' Try this one. It works for me.
SELECT * FROM eventList WHERE DATE(date) BETWEEN '2013-03-26' AND '2013-03-27' 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
