玩转PostgreSQL（六）：Date Functions（方法）

PostgreSQL Date Functions

概览

如果你想节省时间，直接看此图

PostgreSQL AGE 方法

?前言: 在本文中，我们将学习如何使用PostgreSQL AGE()函数计算时间差。

?PostgreSQL AGE 方法

我们通常需要计算应用程序中时间差，例如人员年龄、员工工作时长等。在PostgreSQL中，我们可以使用AGE()方法实现这些功能。

以下说明AGE()方法的语法:

AGE(timestamp,timestamp);

AGE()方法入参是两个timestamp值。它用第一个参数中减第二个参数，并返回一个间隔结果。

请参阅以下示例:

SELECT AGE('2017-01-01','2011-06-24');

输出如下

          AGE
-----------------------
 5 years 6 mons 7 days
(1 row)

如果要将当前日期作为第一个参数，可以使用以下形式的AGE()方法声明:

AGE(timestamp);

例如，如果某人的出生日期2000-01当前日期是2022-10-24，使用AGE 方法计算，他/她的年龄将是:

SELECT current_date, 
       AGE(timestamp '2000-01-01');

    date    |           AGE
------------+-------------------------
 2022-10-24 | 22 years 10 mons 23 days
(1 row)

?PostgreSQL AGE() 方法示例

我们将使用如下表结构作为演示表

假设我们想要查询入职时间最长的十位员工的信息，我们可以借助AGE()方法来查询计算

SELECT staff_id,
         name,
         AGE(join_date) AS duration
FROM staff
WHERE join_date IS NOT NULL
ORDER BY  duration DESC 
LIMIT 10;

在上面的代码中中，使用AGE()函数计算工作年限，基于当前时间和join_date列来计算。

代码输出如下:

 staff_id  | name  | duration
-----------+-------+------------------------
 1337      |Daff   | 49 years 5 mons 17 days
 9353      |ZZr    | 36 years 9 mons 26 days
 3         |Furry  | 14 years 3 mons 20 days
 77        |Tom    | 13 years 11 mons 26 days
 15        |JAck   | 13 years 11 mons 5 days
 773       |Yagao  | 5 years 6 mons 24 days
 417       |Hope   | 4 years 10 mons 24 days
 1242      |Missing| 4 years 7 mons 24 days
 434       |369    | 3 years 11 mons 5 days
 838       |Kanavi | 3 years 11 mons 5 days
 
(10 rows)

?PostgreSQL CURRENT_DATE 方法

PostgreSQL CURRENT_DATE 方法返回当前日期。

?语法

CURRENT_DATE 方法非常简单，不需要参数,使用方法如下:

CURRENT_DATE

?返回值

CURRENT_DATE方法 返回一个 Date类型的数据， 表示当前日期的值。

?示例

以下示例显示如何使用 CURRENT_DATE方法获取当前日期:

SELECT CURRENT_DATE;

输出是一个 Date值如下:

current_date
--------------
2022-10-25

我们可以使用 CURRENT_DATE 用作列的默认值。参考以下示例。

首先， 创建表 命名 prouser 用于演示:

CREATE TABLE prouser(
    prouser_id serial PRIMARY KEY,
    name varchar(255) NOT NULL,
    date DATE DEFAULT CURRENT_DATE
);

在prouser 表中，我们有date字段其默认值是CURRENT_DATE的结果。

第二，插入新行进入prouser表:

INSERT INTO prouser(name) VALUES('369');

在上面的INSERT语句中，我们没有指定date字段的值，因此，PostgreSQL使用当前日期作为默认值。

第三，使用以下SELECT方法验证行是否已成功插入当前日期 :

SELECT * FROM prouser;

下图说明了结果:

如上图所示，当前日期已插入 交付日期 列。

?PostgreSQL CURRENT_TIME 方法

PostgreSQLCURRENT_TIME方法返回带时区的当前时间。

?语法

以下语句说明CURRENT_TIME方法的语法:

CURRENT_TIME(precision)

?参数

CURRENT_TIME方法接受一个可选参数:

1) precision

precision参数指定返回的小数秒精度。如果你不传入precision参数，结果将包括完全可用的精度。

?返回值

CURRENT_TIME方法返回TIME WITH TIME ZONE类型数据，表示具有时区的当前时间的值。

?示例

以下示例显示如何获取当前时间:

SELECT CURRENT_TIME;

返回一个 TIME WITH TIME ZONE 类型的数据如下:

       timetz
--------------------
 03:26:23.414417+00
(1 row)

在此示例中，我们没有指定precision参数，因此，结果中包含了可用的全部精度。

以下示例说明如何使用CURRENT_TIME函数把精度设置为2:

SELECT CURRENT_TIME(2);

输出如下：

     timetz
----------------
 03:27:33.09+00
(1 row)

CURRENT_TIME方法可以用做TIME类型列的默认值.

让我们看看以下示例。

首先，创建用于演示的表命名为demo:

CREATE TABLE demo (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    create_time TIME DEFAULT CURRENT_TIME,
    create_date DATE DEFAULT CURRENT_DATE
);

demo表有created_at列，其默认值为CURRENT_TIME。

第二，插入demo表一条数据:

INSERT INTO demo( name )
VALUES('测试当前时间');

在该语句中，我们仅插入了name列，因此，其他列具有默认值。

第三，检查行是否插入到demo表create_time使用以下命令正确填充列查询:

SELECT * FROM demo;

下图显示了结果:

如图所示，create_time和create_date字段，分别填充了CURRENT_TIME和CURRENT_DATE的默认值。

?PostgreSQL CURRENT_TIMESTAMP 方法

PostgreSQLCURRENT_TIMESTAMP()函数返回带有时区的当前日期和时间，即事务开始的时间。

?语法

下面说明PostgreSQL的语法CURRENT_TIMESTAMP()功能:

CURRENT_TIMESTAMP(precision)

?参数

PostgreSQLCURRENT_TIMESTAMP()方法接受一个可选参数。

1) precision （精度）

precision：在结果的第二个字段中指定小数秒精度中的位数。

如果你省略了precision参数，CURRENT_TIMESTAMP()函数将返回一个TIMESTAMP（包含时区可用的完整小数秒级精度）。

?返回值

CURRENT_TIMESTAMP()方法返回一个带时区的timestamp，表示事务开始的日期和时间。

?示例

以下示例显示如何使用CURRENT_TIMESTAMP()方法获取当前日期和时间:

SELECT CURRENT_TIMESTAMP;

输出如下:

              now
-------------------------------
 2022-10-25 05:09:41.137498+00
(1 row)

在方法内部，CURRENT_TIMESTAMP()是实现now()方法，因此，列别名为now。

和now()功能类似，CURRENT_TIMESTAMP()方法可以用作timestamp列的默认值。

让我们看一下以下示例。

首先，创建表命名rank拥有create_time列，这是一个TIMESTAMP WITH TIME ZONE列。

CREATE TABLE rank(
    rank_id serial PRIMARY KEY,
    rank_name varchar(255) NOT NULL,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

create_time列的默认值结果由提供CURRENT_TIMESTAMP()方法提供。

第二，将新行插入rank表格:

INSERT INTO rank(rank_name) 
VALUES('韩服排位精选，十职业哥混战');

在此语句中，我们没有指定create_time因此，列默认为事务开始时的timestamp。

第三，使用以下查询验证插入是否正确进行:

SELECT
    *
FROM
    rank;

结果如下图所示:

?备注

在PostgreSQL中，TRANSACTION_TIMESTAMP()方法等同于CURRENT_TIMESTAMP方法。但是，函数名TRANSACTION_TIMESTAMP清楚地反映了函数返回的内容。

?PostgreSQL DATE_PART 方法

?PostgreSQL DATE_PART 方法简介

总结: 在本教程中，我们将学习PostgreSQLDATE_PART()函数，它允许我们检索子域，例如：年、月、日、星期、或时间值。

DATE_PART ()函数从日期或时间值中提取子字段。下面说明DATE_PART()功能:

DATE_PART(field,source)

field字段是一个标识符，用于确定从source提取field。字段的值必须在下面提到的列表中:

• century
• decade
• year
• month
• day
• hour
• minute
• second
• microseconds
• milliseconds
• dow
• doy
• epoch
• isodow
• isoyear
• timezone
• timezone_hour
• timezone_minute

source 是求值为 TIMESTAMP, TIME, 或INTERVAL. 如果source的计算结果为DATE, 该函数将转换为 TIMESTAMP.

DATE_PART() 函数返回类型为double 的值。

?PostgreSQL DATE_PART 示例

以下示例从century中提取timestamp:

SELECT date_part('century',TIMESTAMP '2022-10-25');

 date_part
-----------
        21
(1 row)

要从同一timestamp中提取年份，请将'year'传递给field参数:

SELECT date_part('year',TIMESTAMP '2022-10-25');
 date_part
-----------
      2022
(1 row)

要提取季度，请使用以下语句:

SELECT date_part('quarter',TIMESTAMP '2022-10-25');

 date_part
-----------
         4
(1 row)  

要提取月份，请将'month'传递给DATE_PART()方法:

SELECT date_part('month',TIMESTAMP '2022-10-25');
 date_part
-----------
         10
(1 row)

要从timestamp中获取多少个十年，请使用以下语句:

 SELECT date_part('decade',TIMESTAMP '2022-10-25');
 date_part
-----------
       202
(1 row)

要从timestamp中提取周数，请将'week'作为第一个参数传递:

SELECT date_part('week',TIMESTAMP '2022-10-25');
 date_part
-----------
        13
(1 row)

要获得当前的千禧年，我们可以把DATE_PART ()函数和NOW()函数一起使用，示例如下:

 SELECT date_part('millennium',now());
 date_part
-----------
         3
(1 row)

要从timestamp中提取日期部分，请把'day'传递到date_part方法:

SELECT date_part('day',TIMESTAMP '2022-10-25 12:24:48');

 date_part
-----------
        25
(1 row)

要从timestamp中提取小时、分钟、秒，请将相应的值'hour'、'minute'和'second'传递给date_part方法:

SELECT date_part('hour',TIMESTAMP '2022-10-25 12:24:48') h,
       date_part('minute',TIMESTAMP '2022-10-25 12:24:48') m,
       date_part('second',TIMESTAMP '2022-10-25 12:24:48') s;

 h  | m  | s
----+----+----
 12 | 24 | 48
(1 row)

要从timestamp中提取星期几和一年中的某一天，请使用以下语句:

SELECT date_part('dow',TIMESTAMP '2017-03-18 10:20:30') dow,
       date_part('doy',TIMESTAMP '2017-03-18 10:20:30') doy;

 dow | doy
-----+-----
   2 | 298
(1 row)

?PostgreSQL LOCALTIME 方法

PostgreSQLLOCALTIME 函数返回当前事务启动的当前时间。

?语法

LOCALTIME 方法的语法如下所示:

LOCALTIME(precision)

?入参

LOCALTIME函数采用一个可选参数:

1) precision（精度）

precision参数指定第二个字段的小数秒精度。如果省略参数，则默认为6。

?返回值

LOCALTIME函数返回一个TIME值，表示当前事务开始的时间。

?示例

以下查询说明了如何获取当前事务的时间:

SELECT LOCALTIME;

输出如下：

      time
-----------------
 05:52:05.285717
(1 row)

使用指定的小数秒精度获取时间，可以使用以下语句:

SELECT LOCALTIME(2);

输出如下：

time
-------------
 05:52:36.42
(1 row)

?备注

注意： LOCATIME 函数返回一个没有时区的TIME值，而CURRENT_TIME函数返回一个带时区的TIME值。

?PostgreSQL LOCALTIMESTAMP 方法

PostgreSQLLOCALTIMESTAMP函数返回当前事务开始的当前日期和时间。

?语法

以下说明LOCALTIMESTAMP函数的语法:

LOCALTIMESTAMP(precision)

?入参

LOCALTIMESTAMP函数接受一个参数:

1) precision (精度)

precision 参数指定第二个字段的小数秒精度。

precision参数是可选的。如果省略它，则其默认值为6。

?返回值

LOCALTIMESTAMP函数返回一个TIMESTAMP 值，表示当前事务开始的日期和时间。

?示例

以下示例显示如何获取当前事务的当前日期和时间:

SELECT LOCALTIMESTAMP;

输出如下

         timestamp
----------------------------
 2022-10-25 06:13:41.150396
(1 row)

要获取具有特定小数秒精度的当前事务的timestamp，请使用precision 参数如下:

SELECT LOCALTIMESTAMP(2);

输出如下

       timestamp
------------------------
 2022-10-25 06:14:37.45
(1 row)

?备注

LOCALTIMESTAMP函数返回一个不带时区的TIMESTAMP 值

CURRENT_TIMESTAMP函数返回一个带时区的TIMESTAMP值。

?PostgreSQL EXTRACT 方法

PostgreSQLEXTRACT()函数从日期/时间值检索诸如年、月和日的字段。

?语法

以下示例说明EXTRAC()方法的语法:

EXTRACT(field FROM source)

?入参

PostgreSQLEXTRACT()函数需要两个参数:

1) field

field参数指定从日期/时间值中提取哪个字段。

下表说明了有效字段值:

2) source

source 是TIMESTAMP 或INTERVAL类型的值。如果你通过了DATE 值，函数会将其转换为TIMESTAMP 值。

?返回值

EXTRACT() 函数返回double值。

?示例

?A) 从 TIMESTAMP 提取示例

从TIMESTAMP 中提取年份:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

2022

从TIMESTAMP中提取季度:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

4

从TIMESTAMP中提取月份:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

10

从TIMESTAMP中提取日期:

SELECT EXTRACT(DAY FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

31

从TIMESTAMP中提取世纪:

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

21

从TIMESTAMP中提取十年:

SELECT EXTRACT(DECADE FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

201

从TIMESTAMP中提取星期几:

SELECT EXTRACT(DOW FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

2

从TIMESTAMP中提取年中的某一天:

SELECT EXTRACT(DOY FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

298

从TIMESTAMP中获取到从1970-01-01 00:00:00 UTC这个Linux纪元年的开始时间到给定的日期或者timestamp参数的时间之间相隔的秒数:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

1483191015

从TIMESTAMP中提取小时:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

12

从TIMESTAMP中提取分钟:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

24

从TIMESTAMP中提取秒:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2022-10-25 12:24:48.96');

结果包括秒及其小数秒:

48.96

根据ISO 8601提取工作日:

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2022-10-25 12:24:48');

输出如下：

2

从TIMESTAMP中提取毫秒:

SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP '2022-10-25 12:24:48');

结果是 48* 1000 = 48000

48000

从TIMESTAMP中提取微秒:

SELECT EXTRACT(MICROSECONDS FROM TIMESTAMP '2022-10-25 12:24:48');

结果是 48* 1000000 = 48000000

48000000

?B) 从 interval 中 提取示例

从 interval提取年份:

SELECT EXTRACT(YEAR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

6

从 interval提取季度数:

SELECT EXTRACT(QUARTER FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

2

从 interval提取月份:

SELECT EXTRACT(MONTH FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

输出

5

从 interval提取天:

SELECT EXTRACT(DAY FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

4Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

从 interval提取小时:

SELECT EXTRACT(HOUR FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

3

从 interval提取分钟:

SELECT EXTRACT(MINUTE FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

2

从 interval提取秒:

SELECT EXTRACT(SECOND FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

1

从 interval提取毫秒:

SELECT EXTRACT(MILLISECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

1000

从 interval提取微秒:

SELECT EXTRACT(MICROSECONDS FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

1000000

从 interval提取十年:

SELECT EXTRACT(DECADE FROM INTERVAL '60 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

60

从 interval提取对应的秒数:

SELECT EXTRACT(MILLENNIUM FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

1

从 interval提取世纪:

SELECT EXTRACT(CENTURY FROM INTERVAL '1999 years 5 months 4 days 3 hours 2 minutes 1 second' );

输出

19

?PostgreSQL TO_DATE 方法: 将字符串转换为日期

总结: 本节我们将学习使用PostgreSQLTO_DATE()将字符串转换为日期的方法。

?PostgreSQL TO_DATE 方法简介

TO_DATE()函数将一个字符串转换为日期。以下语句说明TO_DATE()的语法:

TO_DATE(text,format);

TO_DATE()函数接受两个字符串参数。第一个参数是要转换为日期的字符串。第二个是输入格式。TO_DATE()函数返回日期值。

请参阅以下示例:

SELECT TO_DATE('20221024','YYYYMMDD');

输出显示:

  TO_DATE
------------
 2022-10-24

在此示例中，字符串20221024根据输入格式转换为日期YYYYMMDD。

• YYYY: 四位数格式的年份
• MM: 两位数格式的月份
• DD:两位数格式的天

结果，该函数返回 2022-10-24.

下表说明了格式化日期值的模板:

?PostgreSQL TO_DATE 示例

以下语句将字符串'25 Oct 2022'转换为日期值:

SELECT TO_DATE('25 Oct 2022', 'DD Mon YYYY');

输出为:

  TO_DATE
------------
 2022-10-25
(1 row)

假设要转换字符串'2022 Oct25'为日期值，我们可以应用模式YYYY Mon DD如下所示:

SELECT TO_DATE('2022 Oct 25','YYYY Mon DD');

该函数返回以下输出:

  TO_DATE
------------
 2022-10-25
(1 row)

?PostgreSQL TO_DATE 踩坑

如果我们传递的日期字符串无效，则TO_DATE()函数将尝试将其转换为有效日期，如果不能，则发出错误。例如:

SELECT TO_DATE('2022/13/33', 'YYYY/MM/DD');

PostgreSQL发出以下错误:

SELECT TO_DATE('2022/13/33', 'YYYY/MM/DD')
> ERROR:  date/time field value out of range: "2022/13/33"

?PostgreSQL TO_TIMESTAMP 方法

?前言: 在本节中，我们将学习如何使用PostgreSQLTO_TIMESTAMP()函数基于指定格式将字符串转换为时间戳。

?语法

以下示例说明TO_TIMESTAMP()方法的语法:

TO_TIMESTAMP(timestamp, format)

?入参

TO_TIMESTAMP()函数需要两个参数:

1) timestamp

timestamp 是一个字符串，表示由指定format格式的timestamp 值.

2) format

构造format字符串，我们可以参考使用以下模板模式来格式化日期和时间值：

?返回值

PostgreSQLTO_TIMESTAMP()函数返回带有时区的timestamp 。

?示例

以下语句使用TO_TIMESTAMP()函数将字符串转换为timestamp:

SELECT TO_TIMESTAMP(
    '2022-10-25 19:30:20',
    'YYYY-MM-DD HH:MI:SS'
);

输出如下

      to_timestamp
------------------------
 2022-10-25 19:30:20-07
(1 row)

在此示例中:

• YYYY是2022年的四位数
• MM是10月
• DD是第25天
• HH是第19小时
• MI是30分钟
• SS是20秒

?备注

1)TO_TIMESTAMP()函数跳过输入字符串中的空格，除非使用固定格式的全局选项 (FX前缀)。

此示例在输入字符串中使用多个空格:

SELECT 
    TO_TIMESTAMP('2022     Oct','YYYY MON');

TO_TIMESTAMP()函数仅省略空格并返回正确的timestamp值:

      to_timestamp
------------------------
 2022-10-01 00:00:00-07
(1 row)

但是，以下示例返回错误:

SELECT 
    TO_TIMESTAMP('2022     Oct','FXYYYY MON');

输出如下

ERROR:  invalid value "" for "MON"
DETAIL:  The given value did not match any of the allowed values for this field.
SQL state: 22007

因为FX前缀要求TO_TIMESTAMP()仅接受一个空格的输入字符串。

2) TO_TIMESTAMP()函数通过最少的错误检查来验证输入字符串。它将尝试尽可能将输入字符串转换为有效的时间戳，有时会产生意外结果。

以下示例使用无效timestamp 值:

SELECT 
    TO_TIMESTAMP('2022-13-32 48:6:66', 'YYYY-MM-DD HH24:MI:SS');

它返回错误:

ERROR:  date/time field value out of range: "2022-13-32 48:6:66"
SQL state: 22008

3) 将字符串转换为timestamp时，TO_TIMESTAMP()函数将毫秒或微秒视为小数点后的秒。

SELECT 
    TO_TIMESTAMP('01-01-2022 10:2', 'DD-MM-YYYY SS:MS');

输出如下

to_timestamp
--------------------------
 2022-01-01 00:00:10.2-07

在此示例中，2不是2毫秒，而是200。意思是:

SELECT 
        TO_TIMESTAMP('01-01-2022 20:2', 'DD-MM-YYYY SS:MS');

并且

SELECT 
        TO_TIMESTAMP('01-01-2022 20:200', 'DD-MM-YYYY SS:MS');

返回相同的结果。

2022-01-01 00:00:20.2-07

要获得2毫秒，必须使用01-2022: 002。在这种情况下，002被解释为0.002秒，相当于2毫秒。

4)如果年份小于四位数，则TO_TIMESTAMP()将其调整到最近的年份，例如，99变成1999，17变成2017。

SELECT
    TO_TIMESTAMP('12 31 99 12:45', 'MM DD YY HH:MI');

输出如下

      to_timestamp
------------------------
 1999-12-31 00:45:00+07
(1 row)

考虑以下示例:

SELECT
      TO_TIMESTAMP('12 31 16 12:45', 'MM DD YY HH:MI');

16的最近年份是2016，因此，它返回以下结果:

to_timestamp
------------------------
 2016-12-31 00:45:00-07

?PostgreSQL NOW 方法

?PostgreSQL NOW() 方法简介

NOW()函数返回当前日期和时间。NOW()方法的返回类型是带时区的timestamp 。参考示例如下:

SELECT NOW();
              now
-------------------------------
 2022-10-25 13:21:36.175627+07
(1 row) 

请注意NOW()函数根据数据库服务器的时区设置返回当前日期和时间。

例如，如果我们将时区更改为‘America/Los_Angeles’:

SET TIMEZONE='America/Los_angeles';

并获取当前日期和时间:

SELECT NOW();
              now
-------------------------------
 2022-10-25 21:21:36.175627+07
(1 row)

如上所示，NOW()输出被调整到新的时区。

如果要获取没有时区的当前日期和时间，可以按如下所示显式转换它:

SELECT NOW()::timestamp;
            now
----------------------------
 2022-10-25 13:21:36.2296335
(1 row)

我们可以使用通用的日期和时间运算符来NOW()功能。例如，从现在开始1小时:

SELECT (NOW() + interval '1 hour') AS an_hour_later;
         an_hour_later
-------------------------------
 2022-10-25 14:21:37.110567-07
(1 row)

要获得明天的这个时间，我们可以在当前时间增加1天:

SELECT (NOW() + interval '1 day') AS this_time_tomorrow;
      this_time_tomorrow
-------------------------------
 2022-10-26 14:21:49.110567-07
(1 row)

要获得4小时15分钟之前，我们可以使用减号 (-) 运算符，如下所示:

SELECT now() - interval '4 hours 15 minutes' AS 415before;

          415before
-------------------------------
2022-10-26 10:6:49.110567-07
(1 row)

?PostgreSQL NOW() 相关方法

除了NOW()方法，我们可以使用CURRENT_TIME或CURRENT_TIMESTAMP获取当前时区日期和时间:

SELECT CURRENT_TIME, CURRENT_TIMESTAMP;
       timetz       |              now
--------------------+-------------------------------
 18:50:51.191353-07 | 2017-03-17 18:50:51.191353-07
(1 row)

要获取没有时区的当前日期和时间，请使用LOCALTIME和LOCALTIMESTAMP函数。

SELECT LOCALTIME, LOCALTIMESTAMP;
      time       |         timestamp
-----------------+----------------------------
 20:27:41.423371 | 2022-10-25 20:27:41.423371
(1 row)

请注意NOW()其相关函数返回当前事务的开始时间。换句话说，函数调用的返回值在事务中是相同的。

以下示例说明了该概念:

postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
              now
-------------------------------
 2022-10-25 20:27:41.423371-07
(1 row)


postgres=# SELECT pg_sleep(3);
 pg_sleep
----------

(1 row)


postgres=# SELECT now();
              now
-------------------------------
 2022-10-25 20:27:41.423371-07
(1 row)


postgres=# COMMIT;
COMMIT

在此示例中，我们将now()函数在事务中使用，其返回值不会在事务中发生变化。

请注意pg_sleep()函数暂停当前会话的进程休眠指定的秒。

如果想获取实时的当前日期和时间，可以使用TIMEOFDAY()功能。考虑以下示例:

SELECT 
    TIMEOFDAY(), 
    pg_sleep(5), 
    TIMEOFDAY();
              timeofday              | pg_sleep |              timeofday
-------------------------------------+----------+-------------------------------------
 Tue Oct 25 20:30:09.216064 2022 PDT |          | Tue Oct 25 20:30:14.217636 2017 PDT
(1 row)

如上所示，暂停5秒后，当前日期和时间增加。

?PostgreSQL NOW() 方法作为默认值

我们可以使用NOW()方法用作表的列的默认值。请参阅以下示例:

首先，创建新表命名rank与create_time具有由提供的默认值的列Now()方法:

CREATE TABLE rank ( 
     id         SERIAL PRIMARY KEY, 
     name       VARCHAR NOT NULL, 
     create_time TIMESTAMPTZ DEFAULT Now() 
);

第二，插入新行进入rank表:

INSERT INTO rank (name) 
VALUES     ('韩服屠杀局！');

第三，从rank表查询:

SELECT * FROM rank;

 id |    name     |          create_time
----+-------------------------+-------------------------------
  1 | 韩服屠杀局！ | 2022-10-25 20:27:26.208497+07
(1 row)

?PostgreSQL DATE_TRUNC 方法

?PostgreSQL date_trunc 方法简介

date_trunc函数基于指定值截断TIMESTAMP 或者INTERVAL 的值日期部分，例如小时、周或月，并以一定的精度返回截断的timestamp 或interval 。

以下示例说明date_trunc()方法的语法:

date_trunc('datepart', field)

'datepart'参数是用于截断field的精度,可以是以下之一:

• millennium
• century
• decade
• year
• quarter
• month
• week
• day
• hour
• minute
• second
• milliseconds
• microseconds

field 是一个TIMESTAMP 或者INTERVAL 要截断的值。它是为求值timestamp或者 interval的表达式。

date_trunc函数返回一个TIMESTAMP 或者INTERVAL 值。

?PostgreSQL date_trunc 示例

以下示例截断TIMESTAMP 到hour 部分:

SELECT DATE_TRUNC('hour', TIMESTAMP '2022-10-25 20:35:30');

输出如下：

     date_trunc
---------------------
 2022-10-25 20:00:00
(1 row)

如果要截断TIMESTAMP 的值为一分钟，可以把'minute'字符串作为第一个参数:

SELECT DATE_TRUNC('minute', TIMESTAMP '2022-10-25 20:35:30');

该函数返回一个TIMESTAMP 其截断到分钟:

 date_trunc
---------------------
 2022-10-25 20:35:00
(1 row)

作者：只注视着你 来源：稀土掘金