SQL克隆和转换日期列 [英] SQL cloning and converting a date column

问题描述

（postgreSQL数据库）

(postgreSQL database)

我有一个日期列，该日期列的格式似乎是 yyyy-mm-dd，首先我想将其转换为

I have a date column that appears to be in the format of 'yyyy-mm-dd' firstly I'd like to convert that said column to 'yyyymm'.

之后，我想将该列克隆到同一表中的另一列，我将其称为年，然后我将其复制到 yyyymm。 d仅需要使用 yyyy格式。

After that I'd like to clone that column to another column within the same table which I'd call "year" and I'd need to have that in the format of 'yyyy' only.

我还没有找到一个适用于我的情况的简单解决方案，这已经足够基本了，因为

I'm yet to find a simple solution that would apply to my case and that's basic enough, as I'm new to SQL in general.

最后，我希望这些列是这样的（不包括其他现有列）

In the end I'd like the columns to be like this (excluding the other existing columns)

   date /*already exists*/  year /*need to create*/
   201501                   2015
   201501                   2015
   201502                   2015
   201606                   2016

干杯

推荐答案

这对您有帮助吗？

这是使用TO_CHAR中内置的日期格式功能。我不完全了解PostgreSQL，所以可能需要做一些调整。

This is using the built in date formatting in the TO_CHAR function. I'm not completely at home with postgresql tho', so some tweaking might need to be done.

UPDATE
    TABLENAME
SET
    year = TO_CHAR(date, 'YYYY'),
    date = TO_CHAR(date, 'YYYYMM');

如果年份列不存在，则必须使用 ALTER TABLE 语句首先

If the column "year" does not already exist, you will have to create it with a ALTER TABLE statement first

ALTER TABLE [table_name] ADD COLUMN [column_name] [data_type] {collation} {column_contstraint};

我建议检查文档以获取正确的用法。

I would recommend checking the documentation for the correct usage.

编辑：如果您担心更新所有行所需的时间（我看到您对另一个答案进行了评论，您回答了大约200万条记录），然后建议阅读这篇文章。

If you are worrying about the time it would take to update all rows (I saw you commented on another answer that you had around 2 million records), then I suggest reading this post.

这说明它可能会更快用数据从头开始创建表，而不是更新每一行。

It explains that it might be quicker to create the table from scratch with the data instead of updating each and every row.

这篇关于SQL克隆和转换日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！