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