如何基于表中date列向同表新增DAY、MONTH、YEAR、QUARTER列？

不同数据库的日期函数语法略有差异，以下是几种主流数据库的具体实现方案：

MySQL/MariaDB #

方式1：永久新增列并赋值 #

如果需要将这四个列永久添加到表结构中，可通过ALTER TABLE新增列，再用UPDATE赋值：

ALTER TABLE your_table_name
ADD COLUMN DAY INT,
ADD COLUMN MONTH INT,
ADD COLUMN YEAR INT,
ADD COLUMN QUARTER INT;

UPDATE your_table_name
SET DAY = DAY(date_column),
    MONTH = MONTH(date_column),
    YEAR = YEAR(date_column),
    QUARTER = QUARTER(date_column);

方式2：查询时生成虚拟列（无需修改表结构） #

若仅需在查询时获取这些字段，直接在SELECT语句中生成即可：

SELECT 
    date_column,
    DAY(date_column) AS DAY,
    MONTH(date_column) AS MONTH,
    YEAR(date_column) AS YEAR,
    QUARTER(date_column) AS QUARTER
FROM your_table_name;

PostgreSQL #

方式1：永久新增列并赋值 #

ALTER TABLE your_table_name
ADD COLUMN DAY INT,
ADD COLUMN MONTH INT,
ADD COLUMN YEAR INT,
ADD COLUMN QUARTER INT;

UPDATE your_table_name
SET DAY = EXTRACT(DAY FROM date_column)::INT,
    MONTH = EXTRACT(MONTH FROM date_column)::INT,
    YEAR = EXTRACT(YEAR FROM date_column)::INT,
    QUARTER = EXTRACT(QUARTER FROM date_column)::INT;

方式2：查询时生成虚拟列 #

SELECT 
    date_column,
    EXTRACT(DAY FROM date_column)::INT AS DAY,
    EXTRACT(MONTH FROM date_column)::INT AS MONTH,
    EXTRACT(YEAR FROM date_column)::INT AS YEAR,
    EXTRACT(QUARTER FROM date_column)::INT AS QUARTER
FROM your_table_name;

SQL Server #

方式1：永久新增列并赋值 #

ALTER TABLE your_table_name
ADD DAY INT,
    MONTH INT,
    YEAR INT,
    QUARTER INT;

UPDATE your_table_name
SET DAY = DAY(date_column),
    MONTH = MONTH(date_column),
    YEAR = YEAR(date_column),
    QUARTER = DATEPART(QUARTER, date_column);

方式2：查询时生成虚拟列 #

SELECT 
    date_column,
    DAY(date_column) AS DAY,
    MONTH(date_column) AS MONTH,
    YEAR(date_column) AS YEAR,
    DATEPART(QUARTER, date_column) AS QUARTER
FROM your_table_name;

注意：将上述代码中的your_table_name替换为你的实际表名，date_column替换为表中的日期列名。

内容的提问来源于stack exchange，提问作者Sara