Saturday, October 26, 2013

time for standards

MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps?

mysql_5.6> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:47:21 | 2013-10-26 15:47:21 | 2013-10-26 15:47:21 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

For the microsecond timestamps we have to specify the fsp or fractional seconds precision, which is an integer between 0 and 6.

mysql_5.6> SELECT NOW(6),SYSDATE(6),CURRENT_TIMESTAMP;
+----------------------------+----------------------------+---------------------+
| NOW(6)                     | SYSDATE(6)                 | CURRENT_TIMESTAMP   |
+----------------------------+----------------------------+---------------------+
| 2013-10-26 15:50:12.378787 | 2013-10-26 15:50:12.378892 | 2013-10-26 15:50:12 |
+----------------------------+----------------------------+---------------------+
1 row in set (0.00 sec)

Please note that you can't specify a fsp for CURRENT_TIMESTAMP.
So how do other databases behave?

PostgreSQL:
dveeden=# SELECT NOW(),CURRENT_TIMESTAMP;
              now              |              now              
-------------------------------+-------------------------------
 2013-10-26 15:55:11.548362+02 | 2013-10-26 15:55:11.548362+02
(1 row)

There is no SYSDATE() function in PostgreSQL (tested with 9.1). And you may not specify a fsp. And you get microseconds by default.

SQLite:
sqlite> select current_timestamp;
2013-10-26 13:57:57
sqlite> select strftime("%Y-%m-%d %H:%M:%f", "now"); 
2013-10-26 13:59:42.408

Version 3.7 doesn't have sysdate() or now(), only current_timestamp and no microseconds by default.

So it seems to be hard to write version and implementation tolerant SQL code. I couldn't easily find any information about what the SQL standards dictate.

There is one trick which could help in some situation:
mysql_5.5> SELECT NOW(/*!50604 6*/);
+---------------------+
| NOW()               |
+---------------------+
| 2013-10-26 16:04:04 |
+---------------------+
1 row in set (0.00 sec)
 
mysql_5.6> SELECT NOW(/*!50604 6*/);
+----------------------------+
| NOW( 6 )                   |
+----------------------------+
| 2013-10-26 16:03:37.136133 |
+----------------------------+
1 row in set (0.01 sec)

Another thrick you might think of is changing the date_time_format and time_format.

mysql_5.6> show global variables like '%time_format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| datetime_format | %Y-%m-%d %H:%i:%s |
| time_format     | %H:%i:%s          |
+-----------------+-------------------+
2 rows in set (0.00 sec)

But that won't work as the documentation points out:
"This variable is unused. It is deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release."

2 comments:

  1. In standard SQL the default timestamp precision is 6, or, as the 9075-2 document puts it: "If timestamp precision is not specified, then 6 is implicit." -- Peter Gulutzan, ocelot.ca/blog

    ReplyDelete
  2. MySQL 5.6 does support fractional seconds precision:

    mysql> select current_timestamp(6);
    +----------------------------+
    | current_timestamp(6) |
    +----------------------------+
    | 2013-10-27 10:15:10.919796 |
    +----------------------------+
    1 row in set (0.00 sec)

    However, the default precision was chosen to be zero, for backwards compatibility.

    ReplyDelete