56

How to select data from mysql table past date to current date? For example, Select data from 1 january 2009 until current date ??

My column "datetime" is in datetime date type. Please help, thanks

Edit:

If let say i want to get day per day data from 1 january 2009, how to write the query? Use count and between function?

4 Answers4

92
select * from *table_name* where *datetime_column* between '01/01/2009' and curdate()

or using >= and <= :

select * from *table_name* where *datetime_column* >= '01/01/2009' and *datetime_column* <= curdate()
Ajit S
  • 1,341
  • 1
  • 13
  • 32
Noah Heldman
  • 6,724
  • 3
  • 40
  • 40
  • Cool. It works. LAST question: If I want to show data per day since 01/01/2009, must use count function right? Any idea how to implement? –  Sep 24 '09 at 04:01
  • You can use "group by" and "order by", like this: select * from [table_name] where [datetime_column] between '01/01/2009' and curdate() group by [datetime_column] order by [datetime_column] – Noah Heldman Sep 24 '09 at 04:06
  • i tried this: SELECT *, count(*) from table WHERE datetime BETWEEN '2009-01-01' AND '2009-09-01' GROUP BY datetime; but the count is 1. it supposed to be, 10, 5, 8 etc... –  Sep 24 '09 at 04:09
  • You have to include the column name you want the count of inside count(). So, you would use count(datetime) in this case. – Noah Heldman Sep 24 '09 at 04:15
  • Also, you have to make sure that you are grouping on fields with the same value. If you have want to group all values for a date regardless of time, you will have to use the DATE() function to get the date part of the datetime. – Noah Heldman Sep 24 '09 at 04:18
  • You will want to group by Day() not just the column because the datetime field goes down to ms I believe which means you will end up with the results grouped at ms instead of day. – CSharpAtl Sep 24 '09 at 04:19
  • "SELECT * , count(datetime) FROM table WHERE datetime BETWEEN '2009-01-01' AND CURDATE() GROUP BY Day(datetime)"; It works.. COOL. My query correct right? Just want to double check? –  Sep 24 '09 at 04:25
  • WAIT. @CSharpAtl: If i group by day, it end up showing 31 days. E.g my date between 1-jan-09 and CURDATE(), but it shows JANuary data only? Why? What should I group????????????? –  Sep 24 '09 at 04:37
  • Problem solved: I group by DATE(datetime). Thanks in millions guys :) –  Sep 24 '09 at 04:38
  • Make sure you add `HH:MM:SS` to **end date** or you won't get any results from that date. – Pedro Lobito Dec 02 '19 at 05:28
26

All the above works, and here is another way if you just want to number of days/time back rather a entering date

select * from *table_name* where *datetime_column* BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)  AND NOW()
shakirthow
  • 1,356
  • 14
  • 15
15

You can use now() like:

Select data from tablename where datetime >= "01-01-2009 00:00:00" and datetime <= now();
GDP
  • 8,109
  • 6
  • 45
  • 82
Kerri
  • 163
  • 6
3

Late answer, but the accepted answer didn't work for me.
If you set both start and end dates manually (not using curdate()), make sure to specify the hours, minutes and seconds (2019-12-02 23:59:59) on the end date or you won't get any results from that day, i.e.:

This WILL include records from 2019-12-02:

SELECT *SOMEFIELDS* FROM *YOURTABLE* where *YOURDATEFIELD* between '2019-12-01' and '2019-12-02 23:59:59'

This WON'T include records from 2019-12-02:

SELECT *SOMEFIELDS* FROM *YOURTABLE* where *YOURDATEFIELD* between '2019-12-01' and '2019-12-02'
Pedro Lobito
  • 94,083
  • 31
  • 258
  • 268