PostgreSql函数和操作符一之时间函数

PostgreSql常用的时间函数

1. 时间间隔age函数

函数描述：计算两个日期之间相隔多少天，单个参数时表示与当前日期(current_date)相比

参数：age(timestamp,timestamp),age(timestamp)

返回值：interval，两个日期之间的相隔天数

示例：

Select age(timestamp '2001-04-10', timestamp '1957-06-13')

Result: 43 years 9 mons 27 days

2. 获取当前日期和时间的关键字和函数

A. Current_date：获取当前的日期

SELECT CURRENT_DATE;

Result：2001-12-23

B. Current_time：获取当前时间

SELECT CURRENT_TIME;

Result: 14:39:53.662522-05

C. Current_tiestamp：获取当前日期和时间，开始于当前的事务

SELECT CURRENT_TIMESTAMP;

Result：2001-12-23 14:39:53.662522-05

D. LOCALTIMESTAMP: 获取当前日期和时间，开始于当前的事务

SELECT LOCALTIMESTAMP;

Result: 2001-12-23 14:39:53.662522

E. Now():获取当前日期和时间，开始于当前事务

注意点：这里的获取当前时间，日期的关键字或者函数都包含了开始于当前的事务，其含义是这个时间的获取的时刻为事务开始的时候，更重要的是在整个事务结束之前这个时间是不会变的，换句话说它在事务的过程中始终是一个值。

示例：

创建自定义函数f_test_time：

create or replace function f_test_time() returns void as $$

begin

raise notice 'time is %',now();

execute 'select pg_sleep(5)';--睡眠5秒

raise notice 'time is %',now();

end;

$$ LANGUAGE 'plpgsql';

Result：

注意:  time is 2015-04-15 10:57:17.588+08

注意:  time is 2015-04-15 10:57:17.588+08

不管两句打印语句之间的睡眠5秒的执行结果，now()函数的值是不会表，直到事务结束。

3. 获取时钟时间

clock_timestamp：获取当前的日期和时间，随着语句的执行而改变

注意点：如果想在function中知道某条sql语句的执行时间，那么需要用这个时钟函数获取时间和日期，总之只有这个函数是随着语句的执行时间实在变化的。

示例：

现将f_test_time函数中添加一条

raise notice 'clock time is %',clock_timestamp();来显示睡眠5秒是否起到了作用。

create or replace function f_test_time() returns void as $$

begin

raise notice 'time is %',now();

execute 'select pg_sleep(5)';--睡眠5秒

raise notice 'time is %',now();

raise notice 'clock time is %',clock_timestamp();

end;

$$ LANGUAGE 'plpgsql';

Result：

注意:  time is 2015-04-15 11:05:40.894+08

注意:  time is 2015-04-15 11:05:40.894+08

注意:  clock time is 2015-04-15 11:05:45.921+08

4. 截取时间

Extract：截取timestamp,存在两个重载函数，

extract(field from timestamp)，

extract(field from interval)

select extract(hour from timestamp '2001-02-16 20:38:40')；

Result：20

这里需要掌握的是field值的选择，不同的需求时不一样的，如：

Century：获取世纪

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2015-04-15 20:38:40');

Result: 21

Day：获取timestamp中的天数和获取时间间隔中的天数

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 16

SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');

Result: 40

Year：获取年

Decade：获取年的值并且除以10

Dow：获取一个星期中的第几天Sunday(0) to  Saturday(6) 

Doy：一年中的第几天

Epoch：距离1970-01-01 00:00:00多少秒

Hour：获取小时数

Minute：获取分钟

Month：获取月份

Quarter：获取季度

Second：获取秒

Timezone：获取时区

Week：获取一年中第几周

还有一个类似extract的函数date_part('field', source)，其field的值一样，field就是纯粹的字符串，如：

SELECT date_part('day', TIMESTAMP '2015-02-16 20:38:40');

Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');

Result: 4

5. 截断时间

date_trunc('field', source)，直接看例子就明白了，

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');

Result: 2001-02-16 20:00:00

结果显示直接将field = hour，即分和秒截断了，如果是hour那么minute和second的值就为00，如果是month那么day=1，如果是year那么month和day均为1。

6. 判断时间是否重合

OVERLAPS：返回bool值，判断两个时间间隔是否重合，只要存在重合即返回true否者返回false。两个重载的方式

(start1, end1) OVERLAPS (start2, end2)

(start1, length1) OVERLAPS (start2, length2)

第一个指start1 到end1和start2到length2两个时间段是否重合

第二个值start1开始加上length1的时间间隔与start2时间开始加上length2的时间间隔，返回两个间隔是否存在重合。

需要注意的是边界值是否被包含，遵循start <= time < end

示例：

     SELECT (DATE '2015-02-16', DATE '2015-12-21') OVERLAPS

      (DATE '2015-10-30', DATE '2015-10-30');

Result: true

SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS

       (DATE '2001-10-30', DATE '2002-10-30'); 

Result: false

SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS

       (DATE '2001-10-30', DATE '2001-10-31');

Result: false

SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS

       (DATE '2001-10-30', DATE '2001-10-31');

Result: true