PostgreSQL 时间/日期学习笔记

PostgreSQL 是一个高度可扩展的关系型数据库，也是一个基于 SQL 的开源数据库。在 PostgreSQL 中，时间和日期在数据处理中起着重要的作用。这篇学习笔记将介绍 PostgreSQL 中时间和日期的基本概念以及在 SQL 查询中如何使用它们。

日期和时间类型

PostgreSQL 支持多种日期和时间类型，包括 date、time、timestamp、interval 等。

DATE

date 类型表示日期，格式为 yyyy-mm-dd。

例如，声明一个 date 类型的变量：

sqlCopy Code
CREATE TABLE example_table (
  id serial PRIMARY KEY,
  date_column date
);

TIME

time 类型表示时间，格式为 hh:mm:ss。

例如，声明一个 time 类型的变量：

sqlCopy Code
CREATE TABLE example_table (
  id serial PRIMARY KEY,
  time_column time
);

TIMESTAMP

timestamp 类型表示日期和时间，格式为 yyyy-mm-dd hh:mm:ss。

例如，声明一个 timestamp 类型的变量：

sqlCopy Code
CREATE TABLE example_table (
  id serial PRIMARY KEY,
  timestamp_column timestamp
);

INTERVAL

interval 类型表示一段时间间隔。

例如，计算两个时间之间的间隔：

sqlCopy Code
SELECT TIMESTAMP '2022-01-01 00:00:00' - TIMESTAMP '2021-01-01 00:00:00' AS year_interval;

时间和日期函数

PostgreSQL 提供了许多处理和操作时间和日期的函数。以下是其中一些常用的函数：

CURRENT_DATE

CURRENT_DATE 函数返回当前系统日期。

sqlCopy Code
SELECT CURRENT_DATE;

CURRENT_TIME

CURRENT_TIME 函数返回当前系统时间。

sqlCopy Code
SELECT CURRENT_TIME;

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 函数返回当前系统日期和时间。

sqlCopy Code
SELECT CURRENT_TIMESTAMP;

EXTRACT

EXTRACT 函数用于从时间戳中提取特定的日期或时间部分，例如年、月、日、小时、分钟等。

sqlCopy Code
SELECT EXTRACT(YEAR FROM TIMESTAMP '2022-01-01 00:00:00') AS year;

DATE_TRUNC

DATE_TRUNC 函数用于将时间戳截断到指定的日期或时间部分，例如年、月、日、小时、分钟等。

sqlCopy Code
SELECT DATE_TRUNC('year', TIMESTAMP '2022-01-01 00:00:00') AS year_start;

实例

以下是一个使用时间戳进行查询和过滤的示例：

sqlCopy Code
CREATE TABLE event_log (
  id serial PRIMARY KEY,
  event_type VARCHAR(255) NOT NULL,
  event_time TIMESTAMP NOT NULL
);

INSERT INTO event_log (event_type, event_time)
VALUES ('login', TIMESTAMP '2022-01-01 10:00:00'),
       ('logout', TIMESTAMP '2022-01-01 12:00:00'),
       ('login', TIMESTAMP '2022-01-02 10:00:00'),
       ('logout', TIMESTAMP '2022-01-03 12:00:00');

SELECT * FROM event_log
WHERE event_time >= TIMESTAMP '2022-01-01 00:00:00'
  AND event_time < TIMESTAMP '2022-01-02 00:00:00'
  AND event_type = 'login';

以上查询将返回在 2022 年 1 月 1 日登录的用户。