postgresql日期相关函数_postgresql 日期函数

一、Postgresql中string转换成timestamp类型

Mybatis+Postgresql

　TO_DATE(#{startTime}, 'YYYY-MM-DD')

    AND op_date <![CDATA[>= ]]>  TO_TIMESTAMP(#{beginTime}, 'YYYY-MM-DD HH24:MI:SS')
    AND op_date <![CDATA[<= ]]>  TO_TIMESTAMP(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')

而页面要接收和传递数据需要在javaBean中写如下

@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Timestamp opDate;        //操作日期

二、PostgreSQL日期加减

在PostgreSQL中可以直接对时间进行加减运算：

SELECT now()::timestamp + '1 year';  --当前时间加1年
SELECT now()::timestamp + '1 month';  --当前时间加一个月
SELECT now()::timestamp + '1 day';  --当前时间加一天
SELECT now()::timestamp + '1 hour';  --当前时间加一个小时
SELECT now()::timestamp + '1 min';  --当前时间加一分钟
SELECT now()::timestamp + '1 sec';  --加一秒钟
 
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';  --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加

三、PostgreSQL常用当前时间

PostgreSQL 提供可许多返回当前日期和时间的函数。部分函数按照当前事务的开始时刻返回结果：

CURRENT_TIME(precision)
transaction_timestamp() 感觉这个描述更符合实际情况

另外部分函数返回实时时间值，在事务中也会随时间变化。
statement_timestamp()
clock_timestamp()
timeofday() 返回的是text字符串

• SELECT ''||now()::timestamp //2022-02-25 14:29:00.189944
• SELECT substring(''||now()::timestamp from 1 for 19)  // 2022-02-25 14:25:44
• select now() //2022-02-25 14:48:35
• select localtime //14:47:08
• select localtimestamp //2022-02-25 14:47:08
• select current_date // 2022-02-25
• select current_time // 14:29:13
• select current_timestamp // 2022-02-25 14:45:03
• select  to_char(now(), 'YYYY-MM-DD HH24:mi:ss:ms') // 2022-02-25 14:35:06:569
• select timeofday() // 返回的是text字符串,Fri Feb 25 14:43:46.005910 2022 CST
• 此外，还有CURRENT_TIME(precision)， statement_timestamp()， clock_timestamp()

四、 PostgreSQL的时间类型

4.1、类型转换函数

    PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

4.2、日期/时间格式化的模式

4.3、数值格式化的模板模式

五、时间/日期函数和操作符

5.1、下面是PostgreSQL中支持的时间/日期操作符的列表：

5.2、日期/时间函数：

5.3、 日期/时间差（EXTRACT/date_part/epoch）

 相对时间差--取自域

• SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2022-02-18 16:50:23'- TIMESTAMP '2022-02-18 16:49:23'));                 //60
• SELECT date_part('MINUTE',cast('2022-02-18 16:50:23' as TIMESTAMP)-cast('2022-02-18 16:49:23' as TIMESTAMP));    //1 

日期之差（绝对）

• SELECT round(date_part('epoch', TIMESTAMP​​​​​​​​​​​​​​ '2020-05-05 11:11:20' - TIMESTAMP '2020-05-05 10:10:10'));   //获取秒差 3670=3600+60+10
• SELECT round(date_part('epoch', TIMESTAMP '2020-05-05 11:11:20' - TIMESTAMP '2020-05-05 10:10:10')/60);   //获取分钟差 61=60+1
• SELECT round(date_part('epoch', TIMESTAMP '2020-05-05 12:11:20' - TIMESTAMP '2020-05-05 10:10:10')/60/60);    //获取小时差 2
• SELECT Date('2022-04-02') - Date('2022-02-01');    //获取天数差 60=31+28+1
• select extract(year from age(TIMESTAMP '2022-04-05',TIMESTAMP '2021-02-04')) * 12  + extract(MONTH from age(TIMESTAMP '2022-04-05',TIMESTAMP '2021-02-04'));    //获取月份差 14=12+2
• SELECT extract(year from age(TIMESTAMP '2022-07-08',TIMESTAMP '2014-07-07'));   //获取年份差  8
• SELECT extract(year from age(TIMESTAMP '2022-07-07',TIMESTAMP '2014-07-07'));   //获取年份差  8
• SELECT extract(year from age(TIMESTAMP '2022-07-06',TIMESTAMP '2014-07-07'));   //获取年份差  7