人大金仓常用日期函数总结（持续更新中）

雨笋情缘

已于 2023-11-08 11:24:36 修改

阅读量4.7k

点赞数 1

分类专栏：数据库人大金仓文章标签：数据库 kingbase 人大金仓

于 2023-06-07 14:08:53 首次发布

本文链接：https://blog.csdn.net/LHS__BRU_745/article/details/131086433

版权

数据库同时被 2 个专栏收录

12 篇文章 1 订阅

订阅专栏

人大金仓

2 篇文章 0 订阅

订阅专栏

1.获取当前时间、当前日期、当前时间戳

SELECT CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

2.天数加减

-- 昨天
SELECT CURRENT_TIMESTAMP - 1

-- 明天
SELECT CURRENT_TIMESTAMP + 1

-- 30日后
SELECT CURRENT_TIMESTAMP + 30

wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

3.月份加减

-- 当前日期1月后
SELECT add_months(CURRENT_DATE, 1)

wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

4.获取指定日期的最小时间

SELECT DATE_TRUNC('day', NOW()) AS today_end;//获取今天的最小时间

5. 获取两个日期时间相差的天数

select
FLOOR( specialDateParam - sysdate) days, specialDateParam - sysdate remainDays
FROM your_table where conditon;

6. 获取本周、本月、本年第一天。

WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:45:00' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual )
SELECT d "Original Date",
trunc(d) "Nearest Day, Time Removed",
trunc(d, 'ww') "Nearest Week",
trunc(d, 'iw') "Start of Week",
trunc(d, 'mm') "Start of Month",
trunc(d, 'year') "Start of Year" FROM dates;

注意：trunc(d, 'iw') "Start of Week" 在R3中不支持，需替换为trunc(d, 'w') "Start of Week",