Menu
I am stuck with a problem in MySQL. I want to get the count of records between two date-time entries.
For example:
I have a column in my table named 'created' having the datetime data type.
I want to count records which were created date-time between "TODAY'S 4:30 AM" and "CURRENT DATE TIME".
I tried some of MySQL's functions but still no luck with it.
Can you please help me with this? thanks.
762
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 .
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';
If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01'; Of course you need to change: table.
Month-difference between any given two dates: Have a look at the TIMESTAMPDIFF() function in MySQL. What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on.
May be with:
SELECT count(*) FROM `table` where created_at>='2011-03-17 06:42:10' and created_at<='2011-03-17 07:42:50'; or use between:
SELECT count(*) FROM `table` where created_at between '2011-03-17 06:42:10' and '2011-03-17 07:42:50'; You can change the datetime as per your need. May be use curdate() or now() to get the desired dates.
138
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
