Once upon a time`stamp`, in a `data`base far far away, someone filed a bug named: `Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP field types.` – Bug Number 8523. This was the beginning of 2005, yet now that we are approaching the end of 2009, after 4.5 years, many (including myself) are still asking for this.

In fairness sake, MySQL have indeed supplied a way to retain milli and micro seconds in a decimal field `DECIMAL(17,3)`, and it is also queryable as if it were a timestamp BUT why isn’t it possible to store in a `DATETIME` or `TIMESTAMP` field? Why can’t we run a ‘select now()’ or ‘select curtime()’ etc and get a full timestamp including milli / micro seconds?

I have counted 37 different usernames asking for this feature, spanning from 15th Feb 2005 to recently. (list found below)

Some have suggested UDFs, others suggested using log4j while others, pretty annoyed, allegedly went to MS SQL and some to Postgres :). The comments were varied but one thing was quite common … a feeling of frustration and or disappointment, probably because despite all the requests for the feature in question to be implemented, it is still missing.

Back in 2007, Kevin Burton also tried to understand why MySQL doesn’t support milliseconds here. Brian Aker gave his view on the matter (back in 2007) in the first comment on the same blog post.

My point is (yeah about time :)) …. this is quite an important feature!! and I’m hoping a few MySQL developers / project managers / community people might read this blog post and maybe give it some more importance.

Now from the technical side, I am going to describing a way, `someone from MySQL has suggested it to me this week` to store milli and micro seconds. Maybe it’ll help you work your milliseconds requirement out (or maybe not). As to the way to generate milli and micro seconds, there is a UDF written by Mr Wadimoff here which you might use.

Storing:


mysql> create table ttable
-> (
-> timecol decimal(17,3)
-> ) Engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into ttable (timecol) values( 20081223094234.572);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttable (timecol) values( 20090806133004.132);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttable (timecol) values( 20100411011258.985);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttable (timecol) values( 20110411011258.985);
Query OK, 1 row affected (0.00 sec)

mysql> select timestamp(timecol) from ttable;
+----------------------------+
| timestamp(timecol) |
+----------------------------+
| 2008-12-23 09:42:34.572000 |
| 2009-08-06 13:30:04.132000 |
| 2010-04-11 01:12:58.985000 |
| 2011-04-11 01:12:58.985000 |
+----------------------------+
4 rows in set (0.00 sec)

mysql> select timecol from ttable where timecol < "20090901000000";
+--------------------+
| timecol |
+--------------------+
| 20081223094234.572 |
| 20090806133004.132 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select timecol from ttable where timecol > "20090901000000";
+--------------------+
| timecol |
+--------------------+
| 20100411011258.985 |
| 20110411011258.985 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select timecol from ttable where timecol > now();
+--------------------+
| timecol |
+--------------------+
| 20100411011258.985 |
| 20110411011258.985 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select datediff(20110411011258.985,20100411011258.985);
+-------------------------------------------------+
| datediff(20110411011258.985,20100411011258.985) |
+-------------------------------------------------+
| 365 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timecol, timestamp(timecol) as timestamp ,year(timecol) as year, month(timecol) as month, day(timecol) as day,quarter(timecol) as quarter from ttable;
+--------------------+----------------------------+------+-------+------+---------+
| timecol | timestamp | year | month | day | quarter |
+--------------------+----------------------------+------+-------+------+---------+
| 20081223094234.572 | 2008-12-23 09:42:34.572000 | 2008 | 12 | 23 | 4 |
| 20090806133004.132 | 2009-08-06 13:30:04.132000 | 2009 | 8 | 6 | 3 |
| 20100411011258.985 | 2010-04-11 01:12:58.985000 | 2010 | 4 | 11 | 2 |
| 20110411011258.985 | 2011-04-11 01:12:58.985000 | 2011 | 4 | 11 | 2 |
+--------------------+----------------------------+------+-------+------+---------+
4 rows in set (0.00 sec)

Bug link: http://bugs.mysql.com/bug.php?id=8523

List of people asking for this feature:

[15 Feb 2005 21:47] Christopher Miller
[03 May 2005 19:46] Boris Burtin
[30 Jun 2005 10:52] Yoshiaki Tajika
[06 Mar 2006 22:14] Tim Sheehy
[14 Mar 2006 21:50] Oriol Garrote
[17 Aug 2006 22:15] Sami Shalabi
[01 Apr 2007 05:22] Ben Valentine
[02 Apr 2007 23:25] Eric George
[27 Apr 2007 06:10] Verghese Mappillai
[23 May 2007 20:47] Andrew McLaughlin
[25 May 2007 18:24] Stephen Pietrowicz
[31 May 2007 10:51] harjeev chug
[22 Jun 2007 01:17] Peter McCulloch
[20 Jul 2007 16:42] Daren Schwenke
[03 Aug 2007 04:45] Jean-Guy Mossu
[20 Feb 2008 09:32] bodri bodri
[11 Mar 2008 20:12] Nathan Atkinson
[24 Mar 2008 08:35] Sourav Sipani
[02 Jul 2008 05:03] Michael Haselton
[22 Aug 2008 10:32] Aniruddha Shival
[28 Aug 2008 17:37] Jorge Urdaneta
[29 Aug 2008 22:19] Ben Wern
[05 Sep 2008 10:41] Pavel Alexeev
[19 Dec 2008 14:31] Felix Dierich
[09 Feb 2009 07:58] Gabriele Tozzi
[17 Feb 2009 23:20] Paul Craven
[21 Feb 2009 12:55] Thomas Ene
[27 Feb 2009 12:13] Pascal Calovini
[09 Mar 2009 09:03] Siu Ching Pong (Asuka Kenji)
[12 Mar 2009 19:41] Yu Chen
[02 Apr 2009 02:35] Noel Akins
[02 Apr 2009 13:09] Lars Monsees
[19 Apr 2009 17:30] Ryan Shillington
[22 Apr 2009 09:00] Jeff Peff
[04 May 2009 09:30] alastair knowles
[28 May 2009 11:42] Yuri Kirilin
[31 May 2009 04:37] Peter Thairu

VN:F [1.9.22_1171]
Rating: 9.5/10 (22 votes cast)
VN:F [1.9.22_1171]
Rating: +24 (from 24 votes)

Once upon a timestamp(milliseconds)…. , 9.5 out of 10 based on 22 ratings

  1. [...] This has been around for a long time, and will be around for a long time. But I did think it’s at least time that we should let people know that we think about it. The specific impetus today was a fairly thorough survey of the feature request’s history in a MySQL DBA’s blog posting Once upon a timestamp(milliseconds)…. [...]

  2. You might want to have a look at: http://blogs.mysql.com/peterg/2009/08/07/fractional-seconds-precision-in-mysql-datetime-data-types/ to learn more about the progress from MySQL’s side.

    Cheers,
    Darren

  3. ChangeData says:

    Hi,

    I’ve found a possible work around to this. Page 230, in a book called “High Performance MySQL”, lists a User-Defined Function (UDF) called “NOW_USEC” this UDF creates a string in the same format as MySQL date, but also includes millisecond precision. The UDF is also listed here (along with installation details):

    http://www.xaprb.com/blog/2007/10/30/how-i-built-the-now_usec-udf-for-mysql/

    I have compiled and linked this UDF to my server and works perfectly. To store the value returned by NOW_USEC() just create a field with data type: varchar(26). I know you’ll have to re-link this UDF every time you upgrade your MySQL server, but it’s the best solution I’ve seen so far.

  4. minghan says:

    Why is the reason for using 17 in DEC(17,3)? Thanks!

  5. With decimal(17,3) you can store the full timestamp including milliseconds. You can then retrieve the date and time (with millisecond) using select timestamp(columnname) from table where columnname is decimal (17,3)

  6. Gagan says:

    Kuch kuch samajh me aaya, shuru ki 2 -3 line achi thi, bad me uper se gayi, ha.. title acha hai, par bollywood ki movie “Once upon a time in mumbai” se copy kiya hai.

  7. [...] MySQL Issue Another consideration is that MySQL timestamps do not support millisecond resolution. If millisecond resolution is needed, you cannot use a Timestamp field. Instead, use a Decimal(17,3) field. Here's a link describing a work around: Once upon a timestamp(milliseconds)…. MySQL Preacher [...]

  8. Jon says:

    I don’t believe this works anymore in MySQL 5+. I’ve tried numerous variations and they all break. It appears that timestamp needs a special format as the input.

    For instance, even this fails:

    select timestamp(’20081223094234.572′)

    I keep getting “Input string was not in a correct format”

  9. It still applied Jon.
    What are you using?

    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 3154
    Server version: 5.5.13 MySQL Community Server (GPL)

    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

    mysql Sat Sep 3 09:08:23 2011 > select timestamp(’20081223094234.572′);
    +———————————+
    | timestamp(’20081223094234.572′) |
    +———————————+
    | 2008-12-23 09:42:34.572000 |
    +———————————+
    1 row in set (0.00 sec)

    mysql Sat Sep 3 09:08:51 2011 > show variables like ‘sql_mode’;
    +—————+——————————————————————————————————————————————————+
    | Variable_name | Value |
    +—————+——————————————————————————————————————————————————+
    | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +—————+——————————————————————————————————————————————————+
    1 row in set (0.02 sec)

    mysql Sat Sep 3 09:08:55 2011 >

  10. Aaron says:

    I love it when MySQL people write like this. Plus it was helpful <3

    Kinda reminds me of this article http://dev.mysql.com/tech-resources/articles/wizard/index.html

*