PostgreSQL教程

获取当前时间

基本时间函数

获取当前日期和时间：

SELECT 
    CURRENT_DATE AS current_date,                         -- 当前日期
    CURRENT_TIME AS current_time,                         -- 当前时间（含时区）
    CURRENT_TIMESTAMP AS current_timestamp,               -- 当前时间戳（含时区）
    NOW() AS now_function,                                -- 同CURRENT_TIMESTAMP
    LOCALTIME AS local_time,                              -- 本地时间（不含时区）
    LOCALTIMESTAMP AS local_timestamp;                    -- 本地时间戳（不含时区）

事务时间

获取事务开始时的时间：

SELECT 
    TRANSACTION_TIMESTAMP() AS transaction_time,          -- 事务开始时间
    STATEMENT_TIMESTAMP() AS statement_time,              -- 语句执行时间
    CLOCK_TIMESTAMP() AS clock_time;                      -- 实时时间（每次调用都不同）

日期时间构造

创建日期时间

从组件构造日期时间：

SELECT 
    DATE '2023-10-15' AS date_literal,                    -- 日期字面量
    TIME '14:30:00' AS time_literal,                      -- 时间字面量
    TIMESTAMP '2023-10-15 14:30:00' AS timestamp_literal, -- 时间戳字面量
    MAKE_DATE(2023, 10, 15) AS made_date,                 -- 构造日期
    MAKE_TIME(14, 30, 0) AS made_time,                    -- 构造时间
    MAKE_TIMESTAMP(2023, 10, 15, 14, 30, 0) AS made_timestamp; -- 构造时间戳

时区处理

处理不同时区的时间：

SELECT 
    TIMEZONE('UTC', CURRENT_TIMESTAMP) AS utc_time,       -- UTC时间
    TIMEZONE('Asia/Shanghai', CURRENT_TIMESTAMP) AS shanghai_time, -- 上海时间
    CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS utc_alt,      -- 另一种UTC表示
    CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai' AS shanghai_alt; -- 另一种上海时间表示

日期时间提取

提取日期部分

从日期时间中提取特定部分：

SELECT 
    EXTRACT(YEAR FROM CURRENT_DATE) AS current_year,      -- 提取年份
    EXTRACT(MONTH FROM CURRENT_DATE) AS current_month,    -- 提取月份
    EXTRACT(DAY FROM CURRENT_DATE) AS current_day,        -- 提取日期
    EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week,        -- 星期几（0=周日）
    EXTRACT(DOY FROM CURRENT_DATE) AS day_of_year,        -- 年中第几天
    EXTRACT(HOUR FROM CURRENT_TIME) AS current_hour,      -- 提取小时
    EXTRACT(MINUTE FROM CURRENT_TIME) AS current_minute,  -- 提取分钟
    EXTRACT(SECOND FROM CURRENT_TIME) AS current_second;  -- 提取秒

日期部分函数

专门的日期部分提取函数：

SELECT 
    DATE_PART('year', CURRENT_DATE) AS year_part,         -- 年份
    DATE_PART('month', CURRENT_DATE) AS month_part,       -- 月份
    DATE_PART('day', CURRENT_DATE) AS day_part,           -- 日期
    DATE_TRUNC('month', CURRENT_DATE) AS truncated_month, -- 截断到月份
    DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS truncated_hour; -- 截断到小时

日期时间计算

日期加减

对日期进行加减运算：

SELECT 
    CURRENT_DATE + INTERVAL '1 day' AS tomorrow,          -- 明天
    CURRENT_DATE - INTERVAL '1 week' AS last_week,        -- 一周前
    CURRENT_DATE + INTERVAL '3 months' AS three_months_later, -- 三个月后
    CURRENT_DATE - '2023-01-01'::DATE AS days_since_new_year; -- 距离元旦天数

年龄计算

计算年龄和时间间隔：

SELECT 
    AGE('1990-01-01'::DATE) AS age_interval,               -- 计算年龄
    AGE('2023-10-15'::DATE, '1990-01-01'::DATE) AS age_between, -- 计算两个日期间隔
    JUSTIFY_DAYS(INTERVAL '35 days') AS justified_days,   -- 规范化天数
    JUSTIFY_HOURS(INTERVAL '25 hours') AS justified_hours; -- 规范化小时

日期时间格式化

格式化输出

将日期时间格式化为字符串：

SELECT 
    TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date, -- 格式化日期
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYY年MM月DD日 HH24:MI:SS') AS chinese_format, -- 中文格式
    TO_CHAR(CURRENT_TIMESTAMP, 'Day, Month DD, YYYY') AS english_format, -- 英文格式
    TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH12:MI:SS AM') AS us_format; -- 美式格式

字符串转日期

将字符串转换为日期时间：

SELECT 
    TO_DATE('2023-10-15', 'YYYY-MM-DD') AS parsed_date,   -- 解析日期字符串
    TO_TIMESTAMP('2023-10-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS parsed_timestamp, -- 解析时间戳
    TO_TIMESTAMP('15/10/2023 02:30:00 PM', 'DD/MM/YYYY HH12:MI:SS AM') AS parsed_us_timestamp; -- 解析美式时间戳

实际应用场景

时间范围查询

-- 查询今天的数据
SELECT * FROM orders 
WHERE DATE(order_date) = CURRENT_DATE;

-- 查询本周的数据
SELECT * FROM orders 
WHERE order_date >= DATE_TRUNC('week', CURRENT_DATE)
  AND order_date < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week';

-- 查询本月的数据
SELECT * FROM orders 
WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE);

-- 查询最近30天的数据
SELECT * FROM orders 
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

时间统计分析

-- 按小时统计订单数量
SELECT 
    EXTRACT(HOUR FROM order_date) AS order_hour,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY EXTRACT(HOUR FROM order_date)
ORDER BY order_hour;

-- 按星期统计订单数量
SELECT 
    TO_CHAR(order_date, 'Day') AS day_of_week,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY TO_CHAR(order_date, 'Day'), EXTRACT(DOW FROM order_date)
ORDER BY EXTRACT(DOW FROM order_date);

业务时间计算

-- 计算订单处理时间
SELECT 
    id,
    order_date,
    shipped_date,
    AGE(shipped_date, order_date) AS processing_time,
    EXTRACT(EPOCH FROM (shipped_date - order_date))/3600 AS processing_hours
FROM orders
WHERE shipped_date IS NOT NULL;

-- 计算用户注册时长
SELECT 
    username,
    created_at,
    AGE(CURRENT_DATE, DATE(created_at)) AS account_age,
    CASE 
        WHEN AGE(CURRENT_DATE, DATE(created_at)) > INTERVAL '1 year' THEN '资深用户'
        WHEN AGE(CURRENT_DATE, DATE(created_at)) > INTERVAL '6 months' THEN '活跃用户'
        ELSE '新用户'
    END AS user_category
FROM users;

时区处理

-- 存储UTC时间，按用户时区显示
SELECT 
    id,
    username,
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' AS shanghai_time,
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS new_york_time
FROM users;

-- 创建带时区的时间戳
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

性能优化

索引优化

为日期时间字段创建合适的索引：

-- 为日期字段创建索引
CREATE INDEX idx_orders_date ON orders (order_date);

-- 为日期范围查询创建索引
CREATE INDEX idx_orders_date_trunc ON orders (DATE_TRUNC('day', order_date));

-- 为时区转换创建函数索引
CREATE INDEX idx_orders_utc_time ON orders (order_date AT TIME ZONE 'UTC');

查询优化

优化日期时间相关的查询：

-- 好的查询（可以使用索引）
SELECT * FROM orders 
WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

-- 不好的查询（可能无法使用索引）
SELECT * FROM orders 
WHERE EXTRACT(MONTH FROM order_date) = 10;

-- 优化后的查询
SELECT * FROM orders 
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- 创建销售报告函数
CREATE OR REPLACE FUNCTION generate_monthly_sales_report(target_month DATE DEFAULT CURRENT_DATE)
RETURNS TABLE(
    report_period TEXT,
    total_orders BIGINT,
    total_sales NUMERIC(15,2),
    avg_order_value NUMERIC(10,2),
    daily_avg_orders NUMERIC(10,2),
    peak_day DATE,
    peak_day_orders BIGINT
) AS $$
DECLARE
    start_date DATE := DATE_TRUNC('month', target_month);
    end_date DATE := DATE_TRUNC('month', target_month) + INTERVAL '1 month';
BEGIN
    RETURN QUERY
    WITH daily_stats AS (
        SELECT 
            DATE(order_date) AS order_day,
            COUNT(*) AS daily_orders,
            SUM(total_amount) AS daily_sales
        FROM orders
        WHERE order_date >= start_date AND order_date < end_date
        GROUP BY DATE(order_date)
    ),
    peak_day_stats AS (
        SELECT 
            order_day,
            daily_orders
        FROM daily_stats
        ORDER BY daily_orders DESC
        LIMIT 1
    )
    SELECT 
        TO_CHAR(start_date, 'YYYY年MM月') AS report_period,
        COUNT(*) AS total_orders,
        SUM(total_amount) AS total_sales,
        ROUND(AVG(total_amount), 2) AS avg_order_value,
        ROUND(AVG(daily_orders), 2) AS daily_avg_orders,
        COALESCE((SELECT order_day FROM peak_day_stats), start_date) AS peak_day,
        COALESCE((SELECT daily_orders FROM peak_day_stats), 0) AS peak_day_orders
    FROM orders o
    LEFT JOIN daily_stats ds ON DATE(o.order_date) = ds.order_day
    WHERE order_date >= start_date AND order_date < end_date;
END;
$$ LANGUAGE plpgsql;

-- 使用函数生成报告
SELECT * FROM generate_monthly_sales_report('2023-10-01');