My question is a restlessness I been having for a time. So, here it goes:

If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of time does the query covers?. From this, I can figure some others like timezone related or daylights changes.

So, have you any knowledge or source to check this information?

I know this is not the kind of filter to apply in a datetime column but due a human mistake in a query once I still have doubts

Thank you

  • Please look I'm not asking how to filter a datetime query. My question is more on the deep understanding of how does MySQL handle a datetime filter when it doesn't have all the data at hand – Alwin Kesler Jun 8 '16 at 5:34

A sargable exact alternative to DATE(ColumnName) = DATE'yyyy-mm-dd' is

ColumnName >= TIMESTAMP'yyyy-mm-dd' AND
ColumnName < TIMESTAMP'yyyy-mm-dd' + INTERVAL '1' DAY

Something like

ColumnName >= 'yyyy-mm-dd 00:00:00' AND
ColumnName <= 'yyyy-mm-dd 23:59:59'

is not only more complicated (and bug prone), but also leaves out any values between 'yyyy-mm-dd 23:59:59.000001' and 'yyyy-mm-dd 23:59:59.999999'.


Looking for date only with DATETIME column, for example:

SELECT * FROM tablename WHERE DATE(ColumnName) = '2003-12-31'

The query above will gives you data with dates of 2003-12-31 excluding the information about the time. As long as you've formatted the DATETIME column into date for 'date only' fetching, time will be ignored.

For using timezone in query, time will be needed with at least of '00:00:00'. I dunno if mysql will give your 'date only' automatic or default time when timezoning, I haven't tested that.


For Date and Time Functions, HERE

  • 1
    Thank you @rhavendc. Please look my answer to RolandoMySQLDBA. By the way this approach is not efficient as it does not work properly with indexes – Alwin Kesler Jun 8 '16 at 5:31
  • @AlwinKesler It's just an example. I didn't recommend anything xD – rhavendc Jun 8 '16 at 5:44

For purposes of comparing DATEs, DATETIMEs, and/or TIMESTAMPs, '2017-09-14', '2017-09-14 00:00:00', and '2017-09-14 00:00:00.000000' are identical.

As for timezone stuff, that is not readily available.

Meanwhile, I wholeheartedly agree with Ziggy's pattern:

ColumnName >= 'yyyy-mm-dd' AND
ColumnName  < 'yyyy-mm-dd' + INTERVAL 1 DAY

(but without the casting).

This takes care of end-of-month, leap-days, etc., and avoids the common mistake of including two midnights in one day. It also works identically regardless of whether ColumName declared DATE, DATETIME, or DATETIME(6); that is, no change to yyyy-mm-dd (assuming you want the boundary to be at midnight).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.