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


§ 1. 语法

CURRENT_TIMESTAMP[([n])]
1

§ 2. 定义和用法

返回当前时间戳。

参数 n 说明:

  1. n的值要小于等于6。
  2. 在ORACLE mode中,CURRENT_TIMESTAMP 等价于 CURRENT_TIMESTAMP(6),小数位是6(n=6),而 CURRENT_TIMESTAMP() 小数位是0(n=0)。而在DEFAULT mode中,CURRENT_TIMESTAMPCURRENT_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. 示例

greatsql> SET sql_mode = ORACLE;
greatsql> SELECT CURRENT_TIMESTAMP;
+------------------------------------+
| CURRENT_TIMESTAMP                  |
+------------------------------------+
| 30-10-23 03:53:18.127314 PM +08:00 |
+------------------------------------+

greatsql> SELECT CURRENT_TIMESTAMP();
+-----------------------------+
| CURRENT_TIMESTAMP()         |
+-----------------------------+
| 30-10-23 03:53:30 PM +08:00 |
+-----------------------------+ 

greatsql> SELECT CURRENT_TIMESTAMP(6);
+------------------------------------+
| CURRENT_TIMESTAMP(6)               |
+------------------------------------+
| 30-10-23 03:53:41.122988 PM +08:00 |
+------------------------------------+

-- 小数位>6时会报错
greatsql> SELECT CURRENT_TIMESTAMP(7);
ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.

greatsql> CREATE TABLE t1(
    id INT,
    c VARCHAR2(64)
);

greatsql> INSERT INTO t1 VALUES(1, CURRENT_TIMESTAMP),
(2, CURRENT_TIMESTAMP()),
(3, CURRENT_TIMESTAMP(3)),
(4, CURRENT_TIMESTAMP(6));

greatsql> SELECT * FROM t1;
+------+------------------------------------+
| id   | c                                  |
+------+------------------------------------+
|    1 | 30-10-23 03:55:12.024380 PM +08:00 |
|    2 | 30-10-23 03:55:17 PM +08:00        |
|    3 | 30-10-23 03:55:26.559 PM +08:00    |
|    4 | 30-10-23 03:55:29.372250 PM +08:00 |
+------+------------------------------------+

greatsql> SET sql_mode = DEFAULT;
greatsql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2023-10-30 15:55:59 |
+---------------------+

greatsql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2023-10-30 15:56:08 |
+---------------------+

greatsql> SELECT CURRENT_TIMESTAMP(6);
+----------------------------+
| CURRENT_TIMESTAMP(6)       |
+----------------------------+
| 2023-10-30 15:56:17.512329 |
+----------------------------+

greatsql> INSERT INTO t1 VALUES(5, CURRENT_TIMESTAMP),
(6, CURRENT_TIMESTAMP()),
(7, CURRENT_TIMESTAMP(3)),
(8, CURRENT_TIMESTAMP(6));

greatsql> SELECT * FROM t1;
+------+------------------------------------+
| id   | c                                  |
+------+------------------------------------+
|    1 | 30-10-23 03:55:12.024380 PM +08:00 |
|    2 | 30-10-23 03:55:17 PM +08:00        |
|    3 | 30-10-23 03:55:26.559 PM +08:00    |
|    4 | 30-10-23 03:55:29.372250 PM +08:00 |
|    5 | 2023-10-30 15:56:31                |
|    6 | 2023-10-30 15:56:34                |
|    7 | 2023-10-30 15:56:37.998            |
|    7 | 2023-10-30 15:56:39.494867         |
+------+------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

扫码关注微信公众号

greatsql-wx