MySQL Join/Comparison on a DATETIME column (<5.6.4 and > 5.6.4)

Question

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:

1. In General, how does MySQL compare datetime fields against one another (consider the above query).
2. Is the above query fine, and does it make use of indexes on the time fields? (MySQL < 5.6.4)
3. Is there any way to exclude milliseconds? I.e. when inserting and in conditional joins/selects etc? (MySQL > 5.6.4)
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?

Answer 1

It seems that MySQL developers didn't want to break backward compatibility, so to use milliseconds, you explicitely have to change your tables, sql, etc. to use this feature:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now

NOW([fsp])

As of MySQL 5.6.4, if the fsp argument is given to specify a fractional seconds precision from 0 to 6,the return value includes a fractional seconds part of that many digits. Before 5.6.4, any argument is ignored.

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

Answer 2

Try this query . For question 3 and 4 this will work fine. Still it is not a good practice to use time field for joins

SELECT E.*,P.* FROM Events E JOIN Position P ON date(E.Time) = date(P.Time)

Although i have given you a solution but you will be restricted to insert the same time in different tables. Then you will be able to compare but it is quite difficult because at the same time you can not run two insert queries. So you will have to do some menuall work for this. If you want to read more read this article.

http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/