PostgreSQL日期/时间函数(兼容oracle)

最新推荐文章于 2024-01-12 15:24:39 发布
最新推荐文章于 2024-01-12 15:24:39 发布
阅读量5.3k 收藏 8
点赞数 1
分类专栏： PostgreSQL 文章标签： sql 数据库 postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39540651/article/details/105809648
版权

在介绍PostgreSQL的时间函数之前，我们先来看一个问题：

bill=# select now(),pg_sleep_for('5 second'),now();
             now              | pg_sleep_for |             now              
------------------------------+--------------+------------------------------
 2020-04-28 10:21:33.77725+08 |              | 2020-04-28 10:21:33.77725+08
(1 row)

上面这个语句为什么中间延迟了5秒，但是两个now函数获取的时间还是一样的呢？如果你不知道什么原因，那么看完这篇文章你就能弄明白了:)

PostgreSQL中的日期/时间函数主要分为两类：一类是支持SQL标准的，另一类是非SQL标准功能的。

SQL标准函数：
sql标准的函数主要有以下几种：

  • CURRENT_DATE
  • CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
  • CURRENT_TIME/CURRENT_TIME(precision)
  • LOCALTIME/LOCALTIME(precision)
  • LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

CURRENT_DATE
current_date返回的是以yyyy-mm-dd格式的日期。

bill=# select current_date;
 current_date 
--------------
 2020-04-28
(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
CURRENT_TIME/CURRENT_TIME(precision)
这些函数返回带时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。

bill=# select CURRENT_TIMESTAMP;
       current_timestamp       
-------------------------------
 2020-04-28 10:30:29.982662+08
(1 row)

bill=# select CURRENT_TIMESTAMP(1);
    current_timestamp     
--------------------------
 2020-04-28 10:30:33.8+08
(1 row)

bill=# select CURRENT_TIME;
    current_time    
--------------------
 10:30:40.337608+08
(1 row)

bill=# select CURRENT_TIME(2);
  current_time  
----------------
 10:30:43.79+08
(1 row)

LOCALTIME/LOCALTIME(precision)
LOCALTIMESTAMP/LOCALTIMESTAMP(precision)
这些函数返回没有时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。

bill=# select LOCALTIMESTAMP;
      localtimestamp       
---------------------------
 2020-04-28 10:31:32.52472
(1 row)

bill=# select LOCALTIMESTAMP(1);
    localtimestamp     
-----------------------
 2020-04-28 10:31:38.1
(1 row)

bill=# select LOCALTIME;
    localtime    
-----------------
 10:31:45.975489
(1 row)

bill=# select LOCALTIME(2);
  localtime  
-------------
 10:31:48.75
(1 row)

非SQL标准函数：

  • transaction_timestamp()
  • statement_timestamp()
  • clock_timestamp()
  • timeofday()
  • now()

transaction_timestamp()和 statement_timestamp()
transaction_timestamp顾名思义返回的是事务的时间，而statement_timestamp函数返回的是语句的时间，区别如下：

bill=# begin;
BEGIN
bill=# select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp     
-------------------------------+-------------------------------
 2020-04-28 10:34:43.811452+08 | 2020-04-28 10:34:42.466875+08
(1 row)

bill=# select pg_sleep(5);
 pg_sleep 
----------
 
(1 row)

bill=# select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp     
-------------------------------+-------------------------------
 2020-04-28 10:34:59.887614+08 | 2020-04-28 10:34:42.466875+08
(1 row)

可以看到在同一个事务中，transaction_timestamp时间都是一样的，而statement_timestamp函数返回的时间是变化的。

clock_timestamp()
clock_timestamp函数返回当前时间的真实时间（clock时间），在同一个语句中返回的值是不同的，例如本文开头的例子：

bill=# select clock_timestamp(),pg_sleep(5),clock_timestamp();
        clock_timestamp        | pg_sleep |        clock_timestamp        
-------------------------------+----------+-------------------------------
 2020-04-28 10:39:08.964432+08 |          | 2020-04-28 10:39:13.965785+08
(1 row)

timeofday()
timeofday函数和clock_timestamp函数类似，但是timeofday返回的是文本格式的数据。

bill=# select clock_timestamp(), pg_typeof(clock_timestamp()), timeofday(), pg_typeof(timeofday());
       clock_timestamp        |        pg_typeof         |              timeofday              | pg_typeof 
------------------------------+--------------------------+-------------------------------------+-----------
 2020-04-28 10:40:16.13543+08 | timestamp with time zone | Tue Apr 28 10:40:16.135431 2020 CST | text
(1 row)

now()
now函数等效于transaction_timestamp，在同一个事务中执行的结果相同。

bill=# begin;
BEGIN
bill=# select now(), transaction_timestamp();
             now              |    transaction_timestamp     
------------------------------+------------------------------
 2020-04-28 10:41:22.71028+08 | 2020-04-28 10:41:22.71028+08
(1 row)

bill=# select pg_sleep(5);
 pg_sleep 
----------
 
(1 row)

bill=# select now(), transaction_timestamp();
             now              |    transaction_timestamp     
------------------------------+------------------------------
 2020-04-28 10:41:22.71028+08 | 2020-04-28 10:41:22.71028+08
(1 row)

看到这里你应该已经知道本文开头的那个问题的答案了吧！
可能你会被这些在同一个语句、同一个事务中返回值不同的函数弄晕了，但其实之所以这些函数返回的结果不同归根结底还是和函数的稳定性有关，例如now和clock_timestamp函数，为什么now函数在同一个事务中返回值一样，其实我们看一下这两个函数的稳定性就知道了。

bill=# \df+ now
                                                                                        List of functions
   Schema   | Name |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code |       Description        
------------+------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+--------------------------
 pg_catalog | now  | timestamp with time zone |                     | func | stable     | safe     | postgres | invoker  |                   | internal | now         | current transaction time
(1 row)

bill=# \df+ clock_timestamp
                                                                                            List of functions
   Schema   |      Name       |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |   Source code   |    Description   
  
------------+-----------------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-----------------+------------------
--
 pg_catalog | clock_timestamp | timestamp with time zone |                     | func | volatile   | safe     | postgres | invoker  |                   | internal | clock_timestamp | current clock tim
e
(1 row)

可以看出，now函数是stable类型的函数，在同一个事务中返回的结果都是一样的，而clock_timestamp函数是volatile类型的函数，所以在同一个事务中返回结果是不同的。
其它的函数也是类似，我们通过其稳定性就可以知道其使用场景了。

oracle兼容：
在oracle中我们经常会用到的日期函数就是sysdate函数了，那么在pg中该如何替换呢？
sysdate显示的是当前系统时间，因此，在长时间运行的事务中，如果有多个SYSDATE函数，则每个语句执行都会返回不同的时间,这个听起来和pg中的clock_timestamp函数很类似。
例如：

SQL> SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
dbms_lock.sleep(30);
DBMS_OUTPUT.PUT_LINE('End : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
 /
Start : 2020-04-27 10:52:50
End : 2020-04-27 10:53:20

可以看到的确和clock_timestamp函数一样。看来，最佳匹配替代SYSDATE的PostgreSQL函数是clock_timestamp()。不过真的如此吗？
oracle中的SYSDATE函数还有一个很重要的特点：SYSDATE对于客户端或会话级时区设置，该功能不起作用!
那么pg中的clock_timestamp函数呢，可以看看下面的例子:

bill=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2020-04-28 10:55:58.072012+08
(1 row)
bill=# set timezone = 'UTC';
SET
bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2020-04-28 02:56:18.983945+00
(1 row)

clock_timestamp函数是和时区有关的！那么我们又该怎么办呢？这个时候我们只好在pg中使用udf自己定义一个sysdate函数来兼容oracle了，其实也很简单，只需要定义一个返回类型是TIMESTAMP WITHOUT TIME ZONE的函数就可以了，例如：

bill=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

bill=# CREATE OR REPLACE FUNCTION sysdate()
bill-# RETURNS TIMESTAMP WITHOUT TIME ZONE
bill-# AS
bill-# $$
bill$#     SELECT clock_timestamp() AT TIME ZONE 'PRC';
bill$# $$
bill-# LANGUAGE sql;
CREATE FUNCTION

这样就完美解决了兼容oracle中sysdate函数的问题了～

验证：

bill=# select sysdate();
          sysdate           
----------------------------
 2020-04-28 11:01:17.345951
(1 row)

bill=# set timezone = 'UTC';
SET
bill=# select sysdate();
          sysdate           
----------------------------
 2020-04-28 11:01:30.404638
(1 row)

参考链接：
https://www.postgresql.org/docs/12/functions-datetime.html

PostgreSQL的学习心得和知识总结（二十九）|PostgreSQL数据库兼容Oracle的SYSDATE函数的实现
孤傲小二~阿沐的博客
05-17 2076
目录结构 注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下： 1、参考书籍：《PostgreSQL数据库内核分析》 2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》 3、PostgreSQL数据库仓库链接，点击前往 4、postgresql内核开发之 SYSDATE实现，点击前往 5、参考书籍：《PostgreSQL中文手册》 1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正） 2、本文目的：开源共享 抛砖引.
PostgreSQL常用时间函数与时间计算提取示例说明
最新发布
m0_54851477的博客
03-10 1039
主要用于计算一些特殊时间，函数说明截断成指定的精度，第1个参数指定精度select date_trunc('year',now()) as 当年第1天,date_trunc('year',now() + interval '1 year') - interval '1 day' as 当年最后1天,date_trunc('month',now()) as 当月第1天,
postgresql 兼容 oracle 函数
05-10
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
postgresql内核开发之 SYSDATE实现
postgres20的博客
12-01 8525
postgresql兼容oracle, postgresql替换oracle，SYSDATE兼容
postgresql 使用 orafce 中的sysdate() 时区不一致问题的解决
悠远的博客
04-01 1362
postgresql 下的now()返回的时间是服务器时间，这个时间是对的。但是装了orafce后的sysdate()就不对，差了一个“服务器时区”，为了这个把服务器时区也改成0时区不合适，于是改改函数吧。。。 反正那个原来也是个函数。从需要使用的用户下面新建这个函数。。。。加上时区。。。。 。。 这个解决办法也不是特别的理想，但是基本上可以解决这个问题了 DROP FUNCT...
postgresql与oracle的时间戳
qq_42478982的博客
07-20 328
postgresql current_timestamp 当前时间戳 时间戳 INSERT_TIME DATE DEFAULT current_timestamp NOT NULL , Oracle 时间戳 "INSERT_TIME" DATE DEFAULT sysdate NOT NULL ENABLE, sysdate 系统时间（postgresql不支持）
postgreSQL格式化时间的函数详解
热门推荐
snn1410的专栏
07-12 27万+
数据类型格式化函数：     PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。 函数 返回类型 描述
PostgreSQL日期/时间函数详解与Oracle兼容
本文档主要介绍了PostgreSQL中的日期/时间函数，特别强调其与Oracle数据库的兼容性。首先，通过一个示例展示了如何使用`pg_sleep_for`函数引发5秒的延迟，但`now()`函数返回的仍是即时时间，解释了函数执行与时间...
PostgreSQL实现USERENV函数(兼容oracle)
12-14
为了在PostgreSQL中实现与Oracle兼容的USERENV功能，我们可以创建自定义函数来模拟这些行为。 首先，我们需要创建一个名为userenv的函数，该函数接受一个参数（anynonarray类型），然后根据传入的参数值返回相应的...
PostgreSQL rownum实现方法(兼容oracle)
12-14
综上所述，虽然PostgreSQL不直接提供`ROWNUM`功能，但通过`LIMIT`、`OFFSET`、序列和窗口函数，我们可以实现与Oracle中`ROWNUM`相同或相似的功能。在进行Oracle到PostgreSQL的数据迁移时，理解这些替代方法是至关...
postgresql的now()与Oracle的sysdate
在路上
06-20 5136
postgresql的now()为当前事务开始时间， 而Oracle的sysdate是当前时间。 区别在于事务。 postgresql中的now()： postgres=# begin ; BEGIN postgres=# select now(); now ------------------------------- 2017-03-
pg 常用日期函数
qq_42807253的博客
10-20 2096
PG常用日期
Postgresql时间操作
该作者帅得被神追
07-03 1196
Postgresql时间操作问题 本机环境 select version(); PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 64-bit 1.date 操作符 select date’20180101’; 局限性: 只能精确到天 2 timestamp 遇到得问题 直接用timestamp 操作字符串拼接函数...
PostgreSQL的时间/日期函数使用
weixin_34099526的博客
04-15 2479
PostgreSQL的常用时间函数使用整理如下： 一、获取系统时间函数 1.1 获取当前完整时间 select now(); david=# select now(); now ------------------------------- 2013-04-12 15:39:40.399711+08 (1 row) ...
PG库日期函数操作
qq_37928228的博客
06-15 4427
代码如下（示例）： 2.日、周、月、年函数 1、格式化日期 select date_trunc(‘day’,CURRENT_TIMESTAMP) 注：day天、week周、month月、year年2、时间推移 –近一周 select now() - interval ‘1 week’;–近一月 select now() - interval ‘1 month’;–近一年 select now() - interval ‘1 year’;–下一周 select now() + interval ‘1 week
PgSQL时间函数
m0_70271240的博客
01-12 2055
1.3 当前时间 - 时间段。1.1 当前时间日期函数。1.2 当前时间+时间段。1.4 时间函数进阶。
postgreSQL返回时间
zy1206_c的博客
09-01 322
sysdate current_date current_time current_timestamp now() 返回服务器的时间 返回当前会话时间 2021-09-01 14:20:40.0 2021-09-01 14:20:40 2021-09-01 14:20:40.050838 2021-09-01 14:20:40.049094 SELECT SYSDATE --服务器当前时间 SELECT SYSD
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值