Oracle兼容-函数-CURRENT_TIMESTAMP()函数

1. 语法

  1. CURRENT_TIMESTAMP[([n])]

2. 定义和用法

返回当前时间戳。

参数 n 说明：

  1. n的值要小于等于6。
  2. 在ORACLE mode中，CURRENT_TIMESTAMP 等价于 CURRENT_TIMESTAMP(6)，小数位是6（n=6），而 CURRENT_TIMESTAMP() 小数位是0（n=0）。而在DEFAULT mode中，CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP() 等价，小数位都是0（n=0）。
  3. 在ORACLE mode中，输出值格式为dd-mm-yy hh:mm:ss[.dec] am|pm timezone，而在DEFAULT mode中，输出值格式为yyyy-mm-dd hh24:mm:ss[.dec]。
  4. 在ORACLE mode中，时间格式与原有的不一样，这会导致部分函数（如UNIX_TIMESTAMP()）当其参数值为CURRENT_TIMESTAMP时会显示异常。

3. 示例

  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> SELECT CURRENT_TIMESTAMP;
  3. +------------------------------------+
  4. | CURRENT_TIMESTAMP                  |
  5. +------------------------------------+
  6. | 30-10-23 03:53:18.127314 PM +08:00 |
  7. +------------------------------------+
  9. greatsql> SELECT CURRENT_TIMESTAMP();
  10. +-----------------------------+
  11. | CURRENT_TIMESTAMP()         |
  12. +-----------------------------+
  13. | 30-10-23 03:53:30 PM +08:00 |
  14. +-----------------------------+ 
  16. greatsql> SELECT CURRENT_TIMESTAMP(6);
  17. +------------------------------------+
  18. | CURRENT_TIMESTAMP(6)               |
  19. +------------------------------------+
  20. | 30-10-23 03:53:41.122988 PM +08:00 |
  21. +------------------------------------+
  23. -- 小数位>6时会报错
  24. greatsql> SELECT CURRENT_TIMESTAMP(7);
  25. ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.
  27. greatsql> CREATE TABLE t1(
  28.     id INT,
  29.     c VARCHAR2(64)
  30. );
  32. greatsql> INSERT INTO t1 VALUES(1, CURRENT_TIMESTAMP),
  33. (2, CURRENT_TIMESTAMP()),
  34. (3, CURRENT_TIMESTAMP(3)),
  35. (4, CURRENT_TIMESTAMP(6));
  37. greatsql> SELECT * FROM t1;
  38. +------+------------------------------------+
  39. | id   | c                                  |
  40. +------+------------------------------------+
  41. |    1 | 30-10-23 03:55:12.024380 PM +08:00 |
  42. |    2 | 30-10-23 03:55:17 PM +08:00        |
  43. |    3 | 30-10-23 03:55:26.559 PM +08:00    |
  44. |    4 | 30-10-23 03:55:29.372250 PM +08:00 |
  45. +------+------------------------------------+
  47. greatsql> SET sql_mode = DEFAULT;
  48. greatsql> SELECT CURRENT_TIMESTAMP;
  49. +---------------------+
  50. | CURRENT_TIMESTAMP   |
  51. +---------------------+
  52. | 2023-10-30 15:55:59 |
  53. +---------------------+
  55. greatsql> SELECT CURRENT_TIMESTAMP();
  56. +---------------------+
  57. | CURRENT_TIMESTAMP() |
  58. +---------------------+
  59. | 2023-10-30 15:56:08 |
  60. +---------------------+
  62. greatsql> SELECT CURRENT_TIMESTAMP(6);
  63. +----------------------------+
  64. | CURRENT_TIMESTAMP(6)       |
  65. +----------------------------+
  66. | 2023-10-30 15:56:17.512329 |
  67. +----------------------------+
  69. greatsql> INSERT INTO t1 VALUES(5, CURRENT_TIMESTAMP),
  70. (6, CURRENT_TIMESTAMP()),
  71. (7, CURRENT_TIMESTAMP(3)),
  72. (8, CURRENT_TIMESTAMP(6));
  74. greatsql> SELECT * FROM t1;
  75. +------+------------------------------------+
  76. | id   | c                                  |
  77. +------+------------------------------------+
  78. |    1 | 30-10-23 03:55:12.024380 PM +08:00 |
  79. |    2 | 30-10-23 03:55:17 PM +08:00        |
  80. |    3 | 30-10-23 03:55:26.559 PM +08:00    |
  81. |    4 | 30-10-23 03:55:29.372250 PM +08:00 |
  82. |    5 | 2023-10-30 15:56:31                |
  83. |    6 | 2023-10-30 15:56:34                |
  84. |    7 | 2023-10-30 15:56:37.998            |
  85. |    7 | 2023-10-30 15:56:39.494867         |
  86. +------+------------------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx