postgresql datetime|极客教程

postgresql datetime

引言

日期和时间是在数据库中经常使用的数据类型之一。PostgreSQL提供了许多函数和操作符来处理日期和时间，使得在查询和分析数据时变得更加方便。本文将介绍 PostgreSQL 中日期和时间的基本知识，并演示如何对其进行操作和计算。

日期和时间的数据类型

在 PostgreSQL 中，日期和时间有多个数据类型可供选择。这些数据类型包括：

DATE：表示日期，如 ‘2022-01-01’。
TIMESTAMP：带有时区的日期和时间，如 ‘2022-01-01 12:00:00+00’。
TIMESTAMPTZ：不带时区的日期和时间，如 ‘2022-01-01 12:00:00’。

日期和时间的输入与输出

在 PostgreSQL 中，可以使用不同的格式来输入和输出日期和时间。常见的格式有：

‘YYYY-MM-DD’：用于输入和输出日期。
‘YYYY-MM-DD HH:MI:SS’：用于输入和输出带有时分秒的日期和时间。
‘YYYY-MM-DD HH:MI:SSOF’：用于输出带有时区的日期和时间。

以下是一些示例：

-- 输入日期
SELECT DATE '2022-01-01';

-- 输入带有时分秒的日期和时间
SELECT TIMESTAMP '2022-01-01 12:00:00';

-- 输出带有时区的日期和时间
SELECT TIMESTAMP WITH TIME ZONE '2022-01-01 12:00:00+00';

-- 输出当前日期和时间
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();

日期和时间的函数和操作符

日期和时间的计算

在 PostgreSQL 中，可以对日期和时间进行加减运算。以下是一些常用的函数和操作符：

+：日期和时间的加法。
-：日期和时间的减法。
AGE(timestamp, timestamp)：计算两个日期之间的时间间隔。
DATE_PART(text, timestamp)：获取日期和时间的部分值（年、月、日、时、分、秒等）。

以下是一些示例：

-- 计算日期和时间的加法
SELECT '2022-01-01'::DATE + 7;  -- 加7天
SELECT TIMESTAMP '2022-01-01 12:00:00' + INTERVAL '1 hour';  -- 加1小时

-- 计算日期和时间的减法
SELECT '2022-01-01' - 7;  -- 减7天
SELECT TIMESTAMP '2022-01-01 12:00:00' - INTERVAL '1 hour';  -- 减1小时

-- 计算两个日期之间的时间间隔
SELECT AGE('2022-01-01', '2022-01-10');  -- 返回 9 days

-- 获取日期和时间的部分值
SELECT DATE_PART('year', '2022-01-01');  -- 返回 2022
SELECT DATE_PART('month', '2022-01-01');  -- 返回 1
SELECT DATE_PART('day', '2022-01-01');  -- 返回 1

日期和时间的格式化

在 PostgreSQL 中，可以使用 TO_CHAR(timestamp, text) 函数将日期和时间格式化为指定的文本格式。以下是一些常用的格式化选项：

'YYYY'：四位数的年份。
'MM'：两位数的月份。
'DD'：两位数的日期。
'HH24'：24小时制的小时。
'MI'：两位数的分钟。
'SS'：两位数的秒。

以下是一些示例：

-- 将日期和时间格式化为指定的文本格式
SELECT TO_CHAR('2022-01-01', 'YYYY-MM-DD');  -- 返回 '2022-01-01'
SELECT TO_CHAR('2022-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');  -- 返回 '2022-01-01 12:00:00'

-- 输出当前日期和时间的格式化结果
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

日期和时间的比较

在 PostgreSQL 中，可以使用比较操作符（如 <, >, =）对日期和时间进行比较。以下是一些示例：

-- 比较日期和时间
SELECT '2022-01-01'::DATE < '2022-01-10'::DATE;  -- 返回 true
SELECT '2022-01-01'::TIMESTAMP < '2022-01-01 12:00:00'::TIMESTAMP;  -- 返回 true

-- 比较当前日期和时间
SELECT CURRENT_DATE < CURRENT_TIMESTAMP;  -- 返回 true