Having a problem getting only records from today in MySQL

blaineca 0
Viewed 36

I have a table set up with these types Columns:

Date_Time datetime PK 
ppm2      int 
ppm10     int 
aqi       int

the query I am using is

SELECT * FROM aqi_data where Date_Time = CURDATE()

This returns 0 records

<?php $chartQuery = "SELECT * FROM aqi_data where Date_Time = CURDATE()"; 
$chartQueryRecords = mysqli_query($con, $chartQuery); 
while($row = mysqli_fetch_assoc($chartQueryRecords)){ 
  echo "'".$row['Date_Time']."',".$row['aqi']. 
       ",".$row['ppm2'].",".$row['ppm10']."],"; 
} ?>

Can anyone help me with this, please?

datetime mysql
1 Answers

You have stored DateTime and you are comparing with date. One way is to cast the data as date

<?php 
$chartQuery = "SELECT * FROM aqi_data where CAST(Date_Time AS DATE) = CURDATE()"; 
$chartQueryRecords = mysqli_query($con, $chartQuery); 
while($row = mysqli_fetch_assoc($chartQueryRecords)){ 
  echo "'".$row['Date_Time']."',".$row['aqi']. 
       ",".$row['ppm2'].",".$row['ppm10']."],"; 
} ?>

Another way, which will be faster is to compare Date_Time with a range between CURDATE and CURDATE + 1DAY.

"SELECT * FROM aqi_data where Date_Time >= CURDATE() and Date_Time < CURDATE()+1";

Not Tested !

Rohit Gupta0
Related