PostgreSQL 教程: time 数据类型

八月 23, 2023

摘要:在本教程中,我们将向您介绍 PostgreSQL 的TIME数据类型,并向您展示一些处理时间值的方便函数。

目录

PostgreSQL TIME 数据类型简介

PostgreSQL 提供了允许您存储一天中的时间值的TIME数据类型。

以下语句说明了如何声明具有TIME数据类型的列:

column_name TIME(precision);

时间值的精度最多可达 6 位数字。精度指定放置在第二个字段中的小数位数。

TIME数据类型需要 8 个字节,允许的范围是从00:00:0024:00:00。下面说明了TIME值的常见格式:

HH:MI   
HH:MI:SS
HHMISS

例如:

01:02
01:02:03
010203

如果要使用精度,可以使用以下格式:

MI:SS.pppppp    
HH:MI:SS.pppppp
HHMISS.pppppp

在这种形式下,p就是精度。例如:

04:59.999999
04:05:06.777777
040506.777777

PostgreSQL 实际上接受几乎任何合理的TIME格式,包括 SQL 兼容格式、ISO 8601 格式等。

PostgreSQL TIME 时间示例

我们经常使用仅存储一天内时间的列的TIME数据类型,例如事件或班次的时间。考虑以下示例。

首先,使用以下CREATE TABLE语句创建一个新表,命名为shifts

CREATE TABLE shifts (
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_at TIME NOT NULL,
    end_at TIME NOT NULL
);

其次,向shifts表中插入一些行

INSERT INTO shifts(shift_name, start_at, end_at)
VALUES('Morning', '08:00:00', '12:00:00'),
      ('Afternoon', '13:00:00', '17:00:00'),
      ('Night', '18:00:00', '22:00:00');

第三步,从shifts表中查询数据

SELECT * FROM shifts;

PostgreSQL TIME 数据类型示例

PostgreSQL 中带时区的时间类型

除了TIME数据类型之外,PostgreSQL 还提供了允许您存储和操作带有时区的时间的TIME with time zone数据类型。

以下语句说明了如何声明数据类型为TIME with time zone的列:

column TIME with time zone

TIME with time zone数据类型的存储大小为 12 个字节,允许您存储范围从00:00:00+145924:00:00-1459的带时区的时间值。

下面列举了一些TIME with time zone值:

04:05:06 PST  
04:05:06.789-8

处理 PostgreSQL TIME 值

获取当前时间

要获取带时区的当前时间,请使用CURRENT_TIME函数,如下:

SELECT CURRENT_TIME;
timetz
--------------------
 00:51:02.746572-08
(1 row)

要获取特定精度的当前时间,请使用CURRENT_TIME(precision)函数:

 SELECT CURRENT_TIME(5);
   current_time
-------------------
 00:52:12.19515-08
(1 row)

请注意,如果不指定精度,CURRENT_TIME函数将返回具有完整可用精度的时间值。

要获取当地时间,您可以使用LOCALTIME函数:

SELECT LOCALTIME;
      localtime
-----------------
 00:52:40.227186
(1 row)

同样,要获取特定精度的本地时间,请使用LOCALTIME(precision)函数:

 SELECT LOCALTIME(0);
 localtime
----------
 00:56:08
(1 row)

将时间转换为不同的时区

要将时间转换为不同的时区,请使用以下形式:

[TIME with time zone] AT TIME ZONE time_zone

例如,要将本地时间转换为 UTC-7 时区的时间,可以使用以下语句:

SELECT LOCALTIME AT TIME ZONE 'UTC-7';
      timezone
--------------------
 16:02:38.902271+07
(1 row)

从时间值中提取小时、分钟、秒

要从时间值中提取小时、分钟、秒,请使用EXTRACT函数,如下所示:

EXTRACT(field FROM time_value);

该字段可以是小时、分钟、秒、毫秒,如下例所示:

SELECT
    LOCALTIME,
    EXTRACT (HOUR FROM LOCALTIME) as hour,
    EXTRACT (MINUTE FROM LOCALTIME) as minute, 
    EXTRACT (SECOND FROM LOCALTIME) as second,
    EXTRACT (milliseconds FROM LOCALTIME) as milliseconds;

PostgreSQL TIME 示例

时间值的算术运算

PostgreSQL 允许您在时间值上以及时间与间隔值之间应用算术运算符,例如 +、- 和 * 。

以下语句返回两个时间值之间的间隔

SELECT time '10:00' - time '02:00' AS result;
  result
----------
 08:00:00
(1 row)

以下语句将当地时间加上 2 小时:

 SELECT LOCALTIME + interval '2 hours' AS result;
    result
-----------------
 03:16:18.020418
(1 row)

在本例中,时间值与间隔值之和为时间值。

在本教程中,您了解了 PostgreSQL 的TIME数据类型,以及如何使用与时间相关的函数处理时间值。

了解更多

PostgreSQL 教程:数据类型

PostgreSQL 文档:日期/时间类型