8

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?

Simon
  • 9,197
  • 13
  • 72
  • 115
  • well for your third question use this DATE(time_column) this will do the trick – Muhammad Raheel Jun 21 '12 at 06:22
  • 1
    I believe `NOW()` in 5.6.4 or later won't factor in milliseconds unless you pass in a parameter (0-6) indicating the "*fractional seconds precision*", so you should be alright for the joining as long as you don't pass in parameters to `NOW()` More [here](http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now) – Zane Bien Jun 21 '12 at 06:47
  • what timezone is used for the DATETIME, dose that timezone have "jumps", like http://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-times-in-1927-giving-a-strange-result – Puggan Se Jul 04 '12 at 18:42
  • The timezone is localized, it will generally be constrained to 2010 and after. Interesting read though! – Simon Jul 05 '12 at 01:21

2 Answers2

5

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.)
biziclop
  • 14,466
  • 3
  • 49
  • 65
1

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/

Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103
  • Could you please explain WHY it is not a good practice to use time field for a join. Thanks – Simon Jun 21 '12 at 06:29
  • i have seen this blog post, and while it invites discussion on using `DATETIME` or `INT` as time storage, it doesn't really address the issues of accuracy when comparing `DATETIME` values. Using a `JOIN` or simple equality comparison. – Simon Jul 04 '12 at 09:08
  • Simple example where joining on a `DATETIME` column may fail: since both inserts cannot happen at the same time, there may be a (small) delay between them. Suppose the first insert is at 19:59:59.999 (note the milliseconds), which may be truncated to 19:59:59, and the second insert is only 1 ms later at 20:00:00.000, which truncates to 20:00:00. Now good luck with your join. This is under the assumption that you are in fact using `NOW()` in your insert queries, not some precalculated time value equal for both queries. – Bart Jul 10 '12 at 08:21