time for standards
来源:互联网 发布:淘宝电影票为什么便宜 编辑:程序博客网 时间:2024/05/16 12:36
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.
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?
For the microsecond timestamps we have to specify the fsp or fractional seconds precision, which is an integer between 0 and 6.
Please note that you can't specify a fsp for CURRENT_TIMESTAMP.
So how do other databases behave?
PostgreSQL:
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:
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:
Another thrick you might think of is changing the date_time_format and time_format.
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."
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:57sqlite> 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."
0 0
- time for standards
- standards
- Website Design With Standards For Browser Compatibility
- International Classification for Standards 国际标准分类号
- MIPI display standards for mobile Internet devices
- MIPI display standards for mobile Internet devices
- Open Standards - a number of web pages for groups producing open standards
- C# Coding Standards for .NET(C#編碼標准,E文的)
- Open Systems And Standards For Software Product Development
- Coding Standards and Best Practices for "appobjects" Files
- Adapting to Web Standards: CSS and Ajax for Big Sites
- More Bug-Killing Coding Standards for Embedded C
- PHP Strict standards: Redefining already defined constructor for class问题
- For the first time!
- The Time for sprint
- Time For Writing
- Need for Time
- test for time.h
- iOS经纬度偏移解决
- Windows NT操作系统
- 面子与钱
- LLVM Language Reference Manual(参考指南)
- 差距的产生
- time for standards
- Module iscsi_trgt not found
- 【转载】优柔寡断的人必读忠告
- 黑马程序员_面向对象基础知识二
- js过滤标签的on事件
- 假设你的月收入2000元,你应该这样用
- 前端框架
- 电脑密码破解
- 使用 LLVM 框架创建一个工作编译器