MySQL Datetime Comparison

mysql datetime comparison

...this is obviously performing a 'string' comparison

No - if the date/time format matches the supported format, MySQL performs implicit conversion to convert the value to a DATETIME, based on the column it is being compared to. Same thing happens with:

WHERE int_column = '1'

...where the string value of "1" is converted to an INTeger because int_column's data type is INT, not CHAR/VARCHAR/TEXT.

If you want to explicitly convert the string to a DATETIME, the STR_TO_DATE function would be the best choice:

WHERE expires_at <= STR_TO_DATE('2010-10-15 10:00:00', '%Y-%m-%d %H:%i:%s')

Mysql Compare two datetime fields

The query you want to show as an example is:

SELECT * FROM temp WHERE mydate > '2009-06-29 16:00:44';

04:00:00 is 4AM, so all the results you're displaying come after that, which is correct.

If you want to show everything after 4PM, you need to use the correct (24hr) notation in your query.

To make things a bit clearer, try this:

SELECT mydate, DATE_FORMAT(mydate, '%r') FROM temp;

That will show you the date, and its 12hr time.

How does MySQL compare datetime to date?

Those aren't dates, they're strings - you might as well be running SELECT 'A' > 'B'. Convert them to DATEs:

SELECT CAST('2018-01-01 00:00:00' AS DATE) > CAST('2018-01-01' AS DATE)

or to DATETIMEs:

SELECT CAST('2018-01-01 00:00:00' AS DATETIME) > CAST('2018-01-01' AS DATETIME)

Casting isn't necessary if you're comparing native MySQL dates coming from a DATE, DATETIME, or TIMESTAMP column - MySQL knows what to do with those. It's only an issue here because you've given it raw strings.

How can I compare two DateTime fields in MySQL?

When comparing two datetimes they will be interpreted as timestamps, so just doing A.datetime > B.datetime should work just fine.

Mysql Compare three datetime fields

I think you can use GREATEST() function

GREATEST(cc.updated, af.created, e.updated) AS latest_date

THis one should give you the latest date for a single row

If you want the latest between all the rows:

MAX(GREATEST(cc.updated, af.created, e.updated)) AS latest_date

that's the same of doing

GREATEST(MAX(cc.updated), MAX(af.created), MAX(e.updated)) AS latest_date

To deal with NULL values you can use COALESCE

GREATEST(
    COALESCE(MAX(cc.updated), 0), 
    COALESCE(MAX(af.created), 0),  
    COALESCE(MAX(e.updated), 0) 
) AS latest_date

Or

 MAX(
    GREATEST(
        COALESCE(cc.updated), 0), 
        COALESCE(af.created), 0),  
        COALESCE(e.updated), 0) 
)) AS latest_date

the result should be the same, maybe they differ in performance, I don't know

Difference comparing dates in MariaDB and MySQL

You should change the query. You are suggesting that '2021-07-14 24:00:00' and '2021-07-15 00:00:00' represent the same value.
Try using this instead:

SELECT * FROM table WHERE start_date >= '2021-07-14 00:00:00' AND start_date <= '2021-07-14 23:59:59'

You could also reduce number of literals in your query:

SELECT * FROM table WHERE DATE(start_date) = '2021-07-14'

Datetime comparison PHP/Mysql?

If your dates are already in MySQL you will want to do the comparison in the query because:

1. MySQL has proper DATE types.
2. MySQL has indexes for comparison.
3. MySQL performs comparisons much faster than PHP.
4. If you filter your data in the query then less, or no time is spent transferring superfluous data back to the application.

Below is the most efficient form. If there is an index on the date column it will be used.

SELECT *
FROM table
WHERE date > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Docs: DATE_SUB()

If you need to do it in PHP:

$now = time();
$target = strtotime($date_from_db);
$diff = $now - $target;
if ( $diff > 900 ) {
  // something
}

or, more succinctly:

if( time() - strtotime($date_from_db) > 900 ) {
  // something
}

Compare two MySQL Datetime columns

SELECT ... WHERE CreatedDate + INTERVAL 12 HOUR < PhotoModificationTimestamp

---

Related Topics