MySQL Join/Comparison on a DATETIME column (<5.6.4 and > 5.6.4)
- by Simon
Suppose i have two tables like so:
Events
ID (PK int autoInc), Time (datetime), Caption (varchar)
Position
ID (PK int autoinc), Time (datetime), Easting (float), Northing (float)
Is it safe to, for example, list all the events and their position if I am using the Time field as my joining criteria? I.e.:
SELECT E.*,P.* FROM Events E JOIN Position P ON E.Time = P.Time
OR, even just simply comparing a datetime value (taking into consideration that the parameterized value may contain the fractional seconds part - which MySQL has always accepted) e.g.
SELECT E.* FROM Events E WHERE E.Time = @Time
I understand MySQL (before version 5.6.4) only stores datetime fields WITHOUT milliseconds. So I would assume this query would function OK. However as of version 5.6.4, I have read MySQL can now store milliseconds with the datetime field.
Assuming datetime values are inserted using functions such as NOW(), the milliseconds are truncated (<5.6.4) which I would assume allow the above query to work. However, with version 5.6.4 and later, this could potentially NOT work. I am, and only ever will be interested in second accuracy.
If anyone could answer the following questions would be greatly appreciated:
In General, how does MySQL compare datetime fields against one another (consider
the above query).
Is the above query fine, and does it make use of indexes on the time
fields? (MySQL < 5.6.4)
Is there any way to exclude milliseconds? I.e. when inserting and in
conditional joins/selects etc? (MySQL 5.6.4)
Will the join query above work? (MySQL 5.6.4)
EDIT
I know i can cast the datetimes, thanks for those that answered, but i'm trying to tackle the root of the problem here (the fact that the storage type/definition has been changed) and i DO NOT want to use functions in my queries. This negates all my work of optimizing queries applying indexes etc, not to mention having to rewrite all my queries.
EDIT2
Can anyone out there suggest a reason NOT to join on a DATETIME field using second accuracy?
