Mysql datetime comparison

sql

datetime-comparison

sql-best-practices

data-types

byAlex Kataev·Jan 11, 2025

Comparisons in MySQL DATETIME are achieved using operators: = for exact comparison, < or > for range comparison. If you want to ignore the time part, use DATE():

-- Did someone say free tacos at exactly this time? I am in!
SELECT * FROM table WHERE datetime_col = '2023-01-01 12:00:00';

-- When you don't care what time the party starts, as long as it's the right day
SELECT * FROM table WHERE DATE(datetime_col) = '2023-01-01';

Ensure to stick to ISO 8601 ('YYYY-MM-DD HH:MM:SS') format for comparisons.

Non-standard date formats or string literals require distinct handling. The STR_TO_DATE() function offers a lifeline for explicit conversion:

-- When your date is a rebel rocking a mohawk
SELECT * FROM table WHERE datetime_col = STR_TO_DATE('01,01,2023','%d,%m,%Y');

MySQL may perform an implicit conversion with a string compared to a DATETIME column.

Spell it out: explicit over implicit

To dodge confusion and potential errors, always be explicit with conversions:

-- Being upfront: "Yes, I am attracted to you... as a compatible date type"
SELECT * FROM table WHERE datetime_col = CAST('2023-01-01' AS DATETIME);

This approach converts the date string into DATETIME type, catering to an accurate comparison. MySQL operates best with DATETIME values set in 'YYYY-MM-DD HH:MM:SS' format.

Between two dates: Use of BETWEEN

In scenarios when you need to check within a range, BETWEEN combined with CAST() is your best bet:

-- What transpired between New Year's and the end of January? Let's find out:
SELECT * FROM table WHERE datetime_col BETWEEN CAST('2023-01-01' AS DATETIME) AND CAST('2023-01-31' AS DATETIME);

This ensures accurate comparison within the DATETIME range.

Know thy types: data types matter

Understanding the data types is paramount. Comparing INT to DATETIME will trigger an implicit conversion of INT to DATETIME. While writing queries involving date/time comparisons, always be sure about the data type of the columns.

Beyond basics: More comparison scenarios

Time zone tango

Applications spanning multiple time zones need a CONVERT_TZ() function to harmonize times to a common time zone:

-- This SQL walked into a timezone bar...
SELECT * FROM table WHERE CONVERT_TZ(datetime_col,'UTC','America/New_York') = '2023-01-01 08:00:00';

Precision is key

For precise time comparisons, look no further than TIMESTAMP data types and functions like CURRENT_TIMESTAMP. These save the day:

-- Who needs a stopwatch when SQL has your back?
SELECT * FROM table WHERE TIMESTAMPDIFF(SECOND, datetime_col, CURRENT_TIMESTAMP) < 60;

The above query extracts records where datetime_col is within the last 60 seconds.

Tread with caution: Avoiding pitfalls

Paying heed to edge cases, like leap years or daylight saving time shifts is a must. Built-in functions like LAST_DAY() helps sidestep wrong calculations:

-- Making sure February doesn't act like it has 30 days
SELECT * FROM table WHERE datetime_col < LAST_DAY('2023-02-01');

explain-codes / Sql / Mysql datetime comparison

Linked

How do I query between two dates using MySQL?



List rows after specific date



Sql "between" not inclusive



Sql query to select dates between two dates



How do I query for all dates greater than a certain date in SQL Server?



Oracle SQL - Date greater than statement



Sql: BETWEEN vs <= and >=



Spell it out: explicit over implicit Between two dates: Use of BETWEEN Know thy types: data types matter Beyond basics: More comparison scenarios