PostgreSQL中如何从秒级时间戳提取年、月、日并生成对应列？

嗨，这个需求在PostgreSQL里实现起来很简单，核心就是先把秒级时间戳转换成数据库能识别的时间类型，再提取对应的年、月、日字段就行。我给你分两种常用场景来说明：

1. 查询时直接返回年、月、日 #

如果只是想在查询结果中得到这三个字段，不用修改表结构，可以用下面的SQL：

方法一：提取数值类型的年、月、日 #

用TO_TIMESTAMP()把秒数转成timestamp类型，再用EXTRACT()提取对应时间部分，返回的是数值：

SELECT
  EXTRACT(YEAR FROM TO_TIMESTAMP(u.updated_at)) AS "year",
  EXTRACT(MONTH FROM TO_TIMESTAMP(u.updated_at)) AS "month",
  EXTRACT(DAY FROM TO_TIMESTAMP(u.updated_at)) AS "day"
FROM "user" u;

针对你给出的时间戳1521533490，执行后会得到：2018、3、20（这个时间戳对应的UTC时间是2018-03-20 15:31:30）。

方法二：提取字符串格式的年、月、日 #

如果需要带前导零的格式（比如月份显示03而不是3），可以用TO_CHAR()格式化：

SELECT
  TO_CHAR(TO_TIMESTAMP(u.updated_at), 'YYYY') AS "year",
  TO_CHAR(TO_TIMESTAMP(u.updated_at), 'MM') AS "month",
  TO_CHAR(TO_TIMESTAMP(u.updated_at), 'DD') AS "day"
FROM "user" u;

这种方式返回的是字符串类型，结果会是'2018'、'03'、'20'。

2. 将结果存入表的对应列 #

如果你的表已经有专门存储年、月、日的列（比如user_year、user_month、user_day，假设是整数类型），可以用UPDATE语句批量写入：

UPDATE "user"
SET
  user_year = EXTRACT(YEAR FROM TO_TIMESTAMP(updated_at))::INTEGER,
  user_month = EXTRACT(MONTH FROM TO_TIMESTAMP(updated_at))::INTEGER,
  user_day = EXTRACT(DAY FROM TO_TIMESTAMP(updated_at))::INTEGER;

这里的::INTEGER是把EXTRACT()返回的数值转换成整数类型，匹配列的类型。如果你的列是字符串类型，换成TO_CHAR()的结果即可。

内容的提问来源于stack exchange，提问作者Paweł Nowicki