时间相关

时间加减

众所周知 Oracle中sysdate+数字表示为+天

时间加减年/月/天/时/分/秒

天/时/分/秒

例如: 加10s

利用+/-间隔时间所在一日中所占的比重

1
2
3
4
5


SELECT
    SYSDATE,
    SYSDATE + (10 / 24 / 60 / 60) AS x
FROM dual;
    ```

利用 interval 文档参考,令人崩溃的官方文档

1
2
3


SELECT SYSDATE,
SYSDATE + INTERVAL '10' SECOND AS x
FROM dual

同理加10分钟如下

1
2
3
4
5
6
7
8


SELECT
    SYSDATE,
    SYSDATE + (10 / 24 / 60 ) AS x
FROM dual;
-- 或者
SELECT SYSDATE,
        SYSDATE + INTERVAL '10' MINUTE AS x
        FROM dual

常用表达式

1
2
3
4
5
6
7
8
9


SELECT SYSDATE,
    SYSDATE + INTERVAL '10' SECOND              AS x1,
    SYSDATE + INTERVAL '10:10' MINUTE TO SECOND AS x2, -- + 10分10秒
    SYSDATE + INTERVAL '10:10' HOUR TO MINUTE   AS x3, -- + 10小时10分钟
    SYSDATE + NUMTODSINTERVAL(2, 'HOUR')        AS x4, -- + 2小时 units.DAY,HOUR,MINUTE,SECOND
    SYSDATE + NUMTOYMINTERVAL(2, 'MONTH')       AS x5, -- + 2月 units.YEAR TO MONTH
    ADD_MONTHS(SYSDATE, 2)                      AS x6, -- + 2月
    ADD_MONTHS(SYSDATE, -2)                     AS x7  -- - 2月
FROM dual;

时间戳

UNIX时间戳（UNIX Time Stamp）为世界协调时间（Coordinated Universal Time，即UTC）1970年01月01日00时00分00秒到现在的总秒数，与时区无关。

生成时间戳

1
2
3
4
5
6
7
8


SELECT TRUNC(
            EXTRACT(DAY FROM intvl) * 24 * 60 * 60
                + EXTRACT(HOUR FROM intvl) * 60 * 60
                + EXTRACT(MINUTE FROM intvl) * 60
                + EXTRACT(SECOND FROM intvl)
    )                 AS 时间戳,
    CURRENT_TIMESTAMP AS 当前utc
FROM (SELECT CURRENT_TIMESTAMP - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') intvl FROM dual);

时间戳转换成UTC

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH base AS (SELECT EXTRACT(DAY FROM intvl) * 24 * 60 * 60
                        + EXTRACT(HOUR FROM intvl) * 60 * 60
                        + EXTRACT(MINUTE FROM intvl) * 60
                        + EXTRACT(SECOND FROM intvl)
                                    AS 时间戳,
                    CURRENT_TIMESTAMP AS 当前utc
            FROM (SELECT CURRENT_TIMESTAMP - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') intvl FROM dual))

SELECT base.当前utc                                                                             AS 当前utc,
    TRUNC(base.时间戳)                                                                       AS 时间戳,
    base.时间戳                                                                              AS 间隔秒数,
    TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(TRUNC(base.时间戳), 'SECOND') AS 结果
FROM base;