MySQL Bugs: #8523: Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP fields

Bug #8523	Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP fields
Submitted:	15 Feb 2005 21:47	Modified:	28 Feb 2005 22:55
Reporter:	Christopher Miller
Status:	Verified
Category:	Server: Types	Severity:	S4 (Feature request)
Version:	5.1	OS:	Linux (Red Hat 9)
Assigned to:		Target Version:
Tags:	bfsm_2006_12_07
Triage:	Triaged: D5 (Feature request)

Description:
Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP field types. 

mysql> CREATE TEMPORARY TABLE TEST ( T1 TIME, T2 TIMESTAMP );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TEST ( T1, T2 ) VALUES ( '12:13:14.000015', '2005-02-16
12:13:14.000015' );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM TEST;
+----------+---------------------+
| T1       | T2                  |
+----------+---------------------+
| 12:13:14 | 2005-02-16 12:13:14 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MICROSECOND(T1), MICROSECOND(T2) FROM TEST;
+-----------------+-----------------+
| MICROSECOND(T1) | MICROSECOND(T2) |
+-----------------+-----------------+
|               0 |               0 |
+-----------------+-----------------+
1 row in set (0.00 sec)

How to repeat:
See above script. 

Suggested fix:
No specific fix recommendation.

I'd also like to see this fixed.  The date functions support microseconds, bow useful are
they if there's no way to *store* a date value with microseconds.

I am using 4.1.10 and I also would like to see this work:

mysql> select now(), DATE_FORMAT( now(), '%H:%i:%S.%f' );
+---------------------+-------------------------------------+
| now()               | DATE_FORMAT( now(), '%H:%i:%S.%f' ) |
+---------------------+-------------------------------------+
| 2005-06-22 15:38:59 | 15:38:59.000000                     |
+---------------------+-------------------------------------+
1 row in set (0.00 sec)

I think so, too. 
As Mysql usage grows, milli-sec and micro-sec become more needed.
For example, 
- recording passage of vast amounts of RFID.
- forensic. I mean andit log of database access for security.
and so on. 
Would you please tell us what this request's priority is for you(mysql), and when we can
get it.

I'd like to see this fixed as I'm trying to set up an auditing database by just dumping
the old records into a history table before updating.  The easiest way to do this is to
key the history table the same as the regular table but add a key based on time. However,
with precision only to the second, only 1 change per second is allowed using this
strategy.

I'd like to see this fixed as I'm trying to set up an auditing database by just dumping
the old records into a history table before updating.  The easiest way to do this is to
key the history table the same as the regular table but add a key based on time. However,
with precision only to the second, only 1 change per second is allowed using this
strategy.

Hi,
I'm trying to make a sport results applicattion with mysql in my website. All my website
is in MySQL, but now I've found the limitation of the TIME data type: it can't retain
fractions of seconds. It's a really strange thing that MS SQL server or  a non commercial
database like PostgreSQL have fractions of seconds precision and MySQL doesn't. Now I'm
considering switching to PostgreSQL.
I hope that developers try to solve it in some way.
Thanks

TIME, TIMESTAMP, and DATETIME do not support parts of seconds (for example          from
DATE_FORMAT()). The simplest way to solve this task is to make a small UDF.

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>

#include <stdio.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>

/* Copyright (c) 2006 Wadimoff <wadimoff@yahoo.com>                            */
/* Created:  27 April 2006                                                            */

/* NOW_MSEC() returns a character string representing the current date and time       */
/* with milliseconds in format YYYY-MM-DD HH:MM:SS.mmm  e.g.: 2006-04-27 17:10:52.129 */

/* How to install:                                                                    */
/* #1  gcc -shared -o now_msec.so now_msec.cc -I /usr/local/include/mysql             */
/* #2  cp now_msec.so /usr/lib                                                        */
/*     Comment : you can copy this wherever you want in the LD path                   */
/* #3  Run this query :                                                               */
/*     CREATE FUNCTION now_msec RETURNS STRING SONAME "now_msec.so";                  */
/* #4  Run this query to test it:                                                     */
/*     SELECT NOW_MSEC();                                                             */
/*     It should return something like that                                           */
/*                                                                                    */
/* mysql> select NOW_MSEC();                                                          */
/* +-------------------------+                                                        */
/* | NOW_MSEC()              |                                                        */
/* +-------------------------+                                                        */
/* | 2006-04-28 09:46:13.906 |                                                        */
/* +-------------------------+                                                        */
/* 1 row in set (0.01 sec)                                                            */

extern "C" {
   my_bool now_msec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
   char *now_msec(
               UDF_INIT *initid,
               UDF_ARGS *args,
               char *result,
               unsigned long *length, char *is_null, char *error);
}

my_bool now_msec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
   return 0;
}

char *now_msec(UDF_INIT *initid, UDF_ARGS *args, char *result,
               unsigned long *length, char *is_null, char *error) {

  struct timeval tv;
  struct tm* ptm;
  char time_string[20]; /* e.g. "2006-04-27 17:10:52" */
  long milliseconds;
  char *msec_time_string = result;
  time_t t;

  /* Obtain the time of day, and convert it to a tm struct. */
  gettimeofday (&tv, NULL);

  t = (time_t)tv.tv_sec;
  ptm = localtime (&t);   /* ptm = localtime (&tv.tv_sec); */

  /* Format the date and time, down to a single second.  */
  strftime (time_string, sizeof (time_string), "%Y-%m-%d %H:%M:%S", ptm);

  /* Compute milliseconds from microseconds. */
  milliseconds = tv.tv_usec / 1000;

  /* Print the formatted time, in seconds, followed by a decimal point
     and the milliseconds.  */
  sprintf(msec_time_string, "%s.%03ld\n", time_string, milliseconds);

  /* Hint: http://www.mysql.ru/docs/man/UDF_return_values.html */

  *length = 23;

  return(msec_time_string);
}

That's all.

I ran into this today too; when I round-trip a timestamp from java to mysql, it comes back
with the miliseconds chopped off ... now I know why.

A limitation that I would love to see addressed...

Here is another vote for a fix

Yet another vote to fix this please!!!
Integer seconds just isn't good enough for many applications, particularly in the
sciences.  Many of my tables now include a datetime column and a float fractional_second
column - but it's an ugly & expensive kludge.

Another suggestion would be conversions to & from astronomical julian dates - but that's
pretty niche.

Thanks

Bug #27838 was marked as a duplicate of this one.

Thought of using log4j JDBC with MySQL. However the fact that milliseconds are not stored
makes it difficult to perform useful reports on the log entries to a MySQL database as so
many entries come in within a second.

Is this bug dead? Does it take more than two years to fix this? When is the planned
rollout of this?

Andrew

I would like to have this bug fix too.  The log files I want to store have time stamps
that contain microseconds, and this information is silently deleted on insertion!

if you couldn'yt fix this bug in 2 yrs then i think i should switch back to MSSql

Speaking as someone who's storing musical information in a database, it would be REALLY
nice to have timing at the microsecond level available.

Another vote for this bug to be fixed.  
I'm currently using timestamp to determine which fields need to be updated for php/ajax
applications, and the dropping of the microseconds creates a possible race condition
during the client update.

Right now, I'm switching back to MS SQL because I really need microseconds in the
timestamp.

But hopefully this will be fixed in a future version and if possible, I would like to
give MySql another try.

Microsecond precision has been pushed into 5.1 tree.

It should be available in 5.1.21.

Thanks for your answer.  It is indeed good news for me.

The patch that has been committed is for microseconds in the slow query log. (Not for all
TIMESTAMP, TIME, and DATETIME types).

I've confirmed that this will be included in 5.1.22.

Hi, I'm not sure I understand.

Is it the "patch that has been committed is for microseconds in the slow query
log" that will be available in 1.5.22 or the possibilité to store microseconds in the
TIMESTAMP, TIME, and DATETIME data types?

Thanks again for your feedback.

Hi Jean-Guy,

I'm sorry for the confusion.  The patch that has been submitted is only to include
microseconds in the slow query log.

It will not include microseconds for TIMESTAMP, TIME, and DATETIME types yet.

Hi Chris,

Thanks again for the quick answer.

Is it possible for you to give us an approximate idea of the timeline for fixing the
Timestamp problem?

I can live for some time without microseconds in the Timestamp, but I know that
eventually I will need those microseconds.

I can imagine that this problem isn't an easy thing to solve, so maybe you don't know
when or have no immediate plans concerning this issue.

Bug #30893 was marked as duplicate of this one.

Bump. Still using seconds since the epoch plus microseconds in an integer field as a
workaround.

Thanks for the UDF NOW_MSEC() to unknown author.

And when would be realized the full support of milliseconds?

and, forgot, I'm pack it UDF into RPM for Fedora (~8). Rpms (both, binaries and src.rpm)
placed in my yum repository: http://hubbitus.org.ru/rpm/MySQL-UDF-now_msec/

I'm logging processes start and end time.
I need the millis because when I sort them I got them in the wrong order if their times
differ only in the millis field.
Please provide this feature in the future.

bodri bodri, UDF NOW_MSEC() as present before working perfectly for this purpose. If you
are logging more faster, and need even more precision for example microseconds instead
milliseconds?? I'm made UDF NOW_MICTOSECOND by example NOW_MSEC of Wadimoff
<wadimoff@yahoo.com>

It is placed here (binaries and sources)
http://hubbitus.net.ru/rpm/Fedora7/MySQL-UDF-now_microsec/
I hope was helpful.

bodri bodri, UDF NOW_MSEC() as present before working perfectly for this purpose. If you
are logging more faster, and need even more precision for example microseconds instead
milliseconds?? I'm made UDF NOW_MICTOSECOND by example NOW_MSEC of Wadimoff
<wadimoff@yahoo.com>

It is placed here (binaries and sources)
http://hubbitus.net.ru/rpm/Fedora7/MySQL-UDF-now_microsec/ ,
http://hubbitus.net.ru/rpm/Fedora8/MySQL-UDF-now_microsec/
I hope was helpful.

I really would love to see this bug fixed as well, I am a relativley basic user, and
uploading a UDF is way out of my league.

Nathan Atkinson, I'm not a developer of mysql, but installing UDF is much easier than it
might seems. Only 4 steeps, as wrote Wadimoff in comment befor, or much easier if you
using Fedora - just install my binary packages - this is 1 command (but, you will need
root privileges in any case)!

Another vote for this bug to fixed. Microseconds would be ideal but milliseconds are a
must.

Another vote for this bug to be fixed. Microseconds would be ideal but milliseconds are a
must.

Another vote to see this is fixed.

Milliseconds are really useful. Please fix this bug as early as possible.

Yet another vote! please fix this bug

Why UDF is not suitable? Performance issue or why???

Do the UDF solutions referenced above simply allow NOW to be invoked with milli or micro
second precision, or do they allow storage at that level of detail?

>Do the UDF solutions referenced above simply allow NOW to be invoked with milli
>or micro second precision
No. But it is not needed, IMHO. So, if required this precision you may use additional
functions with (or instead of) NOW. By default they precision is overhead and also will
work slowly.

>or do they allow storage at that level of detail?
I don't understand this question. Why you can't store any value from any function
anywhere?
If you talk about store datetime value in one field with that precision, yes, it is not
allowed now in default types, but may be easy emulated (f.e. in text representation)

Pavel,

I'm not sure I understand what you are saying in terms of it not being needed - it's
certainly needed for a LOT of applications that I can think of. In my particular case,
PLCs that need to log a LOT of data with very precise time/dates.

Wouldn't storing this level time data in a text field be very expensive to
sort/index/search on as opposed to a dedicated time field (datetime or similar)? I guess
my concern with inserting datetime into a text field is that the selects to recall the
data are going to kill my performance.

Ben Wern,

I'm say what not needing change NOW to get this precision, not provide this possibility
at all.
NOW used ubiquitously, and many places microseconds not needed. So, adding it only cause
old applications to work slowly.

About performance of string representation - I do not make any tests or benchmarks. But,
I guess what it is not be very slow. In any case, if this overhead do not acceptable, you
may use any other representation such us addition integer field for microseconds and use
them in operations together with datetime.

My workaround, instead of text date, is to use an int(14) and populating it with a custom
integer timestamp: ( unix timestamp with microseconds ) * 10000

Indexing and arithmetic are efficient.  As long as we remember the factor of 10000 when
coding, it works just fine.

This is a compromise with trade offs.  I can't seem to get anything in the Windows build
of 5.0.51b (upgrading is not an option) to yield fractional seconds from the system
clock, so the custom timestamp has to be generated outside of the database.  In this
case, the application server generating the timestamp is on a different machine.

So, since I can't use the db server's clock as authoritative, conflicts will eventually
arise when the application servers' and the db server's clocks diverge.

To add another view:

We are using MySQL and other DBs (e.g. Oracle) with Hibernate with the same application.
It was quite a surprise to learn that MySQL does not support milliseconds - this is
pretty much a knock-out criterion for MySQL with some of our applications, e.g. for log
keeping applications or configuration change tracking application. 

We have applied workarounds like extra floating point columns for some software, but this
is not possible in a clean way when you use Hibernate to abstract from the DB. Also
patching the MySQL code would destroy some of our customer's acceptance for MySQL - most
insist on Oracle anyway and it is hard to persuade them to go for something else. 

Although MySQL is otherwise a great product, things like this will not make it easier for
commercial acceptance of MySQL, especially in large industrial applications. I can
understand that changing the datatype implementation in a software like MySQL has massive
implications, but I'm sure it would be worth it for the future of the product.

I've posted a hacky solution for storing milliseconds with datetime values here.
http://90kts.com/blog/2009/storing-milliseconds-in-mysql/

Hope that helps.

Another vote to see this is fixed.

Do the developers have any timescales for fixing this?
It really is a huge hole in MySQL....

I would like to see this fixed as well in an official release.

Thomas

Another vote to see this is fixed.

Another vote to see this is fixed.

even in Excel I can fix it with formatting to hh:mm:ss,000

seems to be very hard to be fixed after 3 years.

Another vote here.

If the microsecond value could not be stored easily because of compatibility reasons,
etc., it should at least be retrieve-able, so that the users have a choice of storing it.

Another vote here.

If the microsecond value could not be stored easily because of compatibility reasons,
etc., it should at least be retrieve-able by a built-in function, so that the users have
a choice of storing it.

We need this one fixed ASAP! We can't differentiate transactions happens within a second.

Just a little tip:
If you use Hibernate or som other ORM/Data mapping, just define your own Data Type and
you are done... very simple. 
You just store the date in milliseconds in the database and let the Date Type convert it
back and forward to a Date object.

Just my thoughts...
Ed

Hi Ed,
do you have any links that further explain your proposal?

+1 for fixing this bug

Lars

Here it is.
Have a look at this:
http://hibernate.org/272.html
Here a user type is created to store/load java Enum data types directly in/from the
database.

You can do exactly the same as with the java Data type.

In short:
Write your own Date user type, something like MyDate user type. 
This class extends from implements EnhancedUserType and MyDate will be asked by Hibernate
to fill the Date object. You then simple load the millesoncds from the database from some
numberic database field and create a new Date object with it. And visa versa... 
Takes you really nothing to implement this and works very well.

Usage: in you hibernate mapping file you define the user type with the typedef tag and
give it a name. This name is then used as type attribute in the property tag... 
That's it... isn't that nice ;)

Good luck,
Ed

I'm not the best coder in the world. I have to work the asp.net and mysql. And, I
generally love mysql. But, I ran into a issue trying to use DateTime as the base for page
updates. I was really shocked to find out the mysql wouldn't store fractional seconds. I
find it hard to believe that this issue hasn't be resolved in 5.0. I hope this gets some
attention soon.

Hi Ed,

thanks! It was quite hard to implement as I am new to Hibernate and additionally had to
switch from xdoclet to annotations.

The complete solution can be found at the Hibernate forums:
http://forum.hibernate.org/viewtopic.php?p=2409970#2409970

hth,
Lars

He Lars,

Great to hear that it worked. Always nice to hear to some feedback.

-- Ed

My bully postgres friends made fun of me saying MySQL isn't a real database.  They said,
"It doesn't even store miliseconds!".  "Try out DBase or something from the late
seventies if I wanted a better database."

I thought surely they were missing something.  There's probably an alternate type that
they don't know about.

Sure enough, I find myself logging a queue of events, having to read them back months and
decipher which one came first.  I guess I need need to use PostgreSQL?

Seriously, what are you putting into MySQL 6 that's more important than this?  

Sincerely,

Die-hard MySQL fan

Please for pete's sake... FIX this :(

Now that Oracle toke over Sun, it will proabably never be fixed ;)....

I switched to Postgresql. Performs much better without bugs in case you need correct
transactions.

-- Ed

Not sure if there is any point adding to this, given the number of comments. Anyway, I am
using MySQL to build a peer to peer application, and I want to use date/time as my
primary key, as it gives me a simple, growable key space. I need milli/microsecond
precision. Really surprised this is not supported.

Yet another vote to have this feature realized

Please fix this soon!!

Bug#31013 is related to this bug.

I too need this feature and blogged about it here:
http://mysqlpreacher.com/wordpress/2009/08/once-upon-a-timestampmilliseconds/

Guys please also read:
http://blogs.mysql.com/peterg/2009/08/07/fractional-seconds-precision-in-mysql-datetime-da...
and vote on: http://forge.mysql.com/worklog/task.php?id=946

This will help shift this feature higher up in the "important features to be developed"
list.

Cheers,
Darren