How to Compare Timestamp Dates With Date-Only Parameter in MySQL

How to compare timestamp dates with date-only parameter in MySQL?

You can use the DATE() function to extract the date portion of the timestamp:

SELECT * FROM table
WHERE DATE(timestamp) = '2012-05-25'

Though, if you have an index on the timestamp column, this would be faster because it could utilize an index on the timestamp column if you have one:

SELECT * FROM table
WHERE timestamp BETWEEN '2012-05-25 00:00:00' AND '2012-05-25 23:59:59'

How to compare Timestamp in where clause

SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no 
FROM users 
LEFT JOIN tahminler ON users.id = tahminler.user_id  
where year(timestamp) = 2013 and month(timestamp) = 9
GROUP BY users.id 
having count(tahminler.tahmin) > 0

To make it work with indexes you can do

SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no 
FROM users 
LEFT JOIN tahminler ON users.id = tahminler.user_id  
where timestamp >= '2013-09-01' and timestamp < '2013-10-01'
GROUP BY users.id 
having count(tahminler.tahmin) > 0

Comparing datetime pattern 'y-M-d H:m:s' select condition with Date type

what i what know "2019-12-26 00:00:00.0" is convert to which type to compare with attendance_date or what will mysql do when date datetime timeStamp type compare with the 'yyyy-MM-dd HH:mm:ss' pattern string

When operands have different but compatible datatypes in comparing operation then they are converted to the same datatype which guarantees that data loss or change will not occur.

So DATE type value is converted to DATETIME type in such case, and not backward (converting '2019-12-26 13:00:00.0' to DATE will alter the value).

To obtain the result which you need (does the literal is a parameter really?) you may convert the literal to DATA type explicitly:

and signStu.attendance_date >= DATE('2019-12-26 13:00:00.0')

Compare date and time to current time in MySQL

You can get cuttent_date() and current_time() like this:

$sql = "SELECT * FROM p_appointment WHERE date >= current_date() AND time >= current_time() ORDER BY date,time ASC

Also you shouldn't fetch all data from database if you have a lot of records (>100). It is a good practice to use LIMIT to get only part of records and perform pagination for records.

Check the year of timestamps

You have a couple of problems with your code, first of all you are returning an assignment

return $query = $this->createQue...

also you set the same parameter value to both :start and :end, the string "$year-01-01" which will not match the stored timestamps, and you don't change the value of $year in between so even if it could match it would be an empty range.

You need to determine the starting and ending timestamp for the given year and use them as parameters for the query.

public function dateOfBirth(string $year): array {
    // get the DateTimes
    $startDate = new \DateTime("midnight January 1, $year");
    $year += 1;
    $endDate = new \DateTime("midnight January 1, $year");
    // get the timestamps
    $start = $startDate->format('U');
    $end = $endDate->format('U');

    return $this->createQueryBuilder('p')
            ->select('p.uid')
            ->where("p.dateOfBirth > :start")
            ->andWhere("p.dateOfBirth < :end")
            ->setParameter('start', $start)
            ->setParameter('end', $end)
            ->getQuery()
            ->getResult();
}

---

Related Topics