将单独的年，月，日，时，分和秒列集成为单个时间戳列 [英] Integrate separate year, month, day, hour, minute and second columns as a single timestamp column

问题描述

我的数据集包含以下分别由空格分隔的年，月，日，时，分和第二列，如下所示：

My data set contains separate year, month, day, hour, minute, and second columns as following that are separated by space:

+-------------------+
|2007|09|28|21|14|06|
|2007|09|28|21|14|06|
|2007|09|28|21|14|06|
|2007|09|28|21|14|06|
|2007|09|28|21|14|06|
+-------------------+

我想将它们集成为timestamp数据类型下的单个列。
我创建了一个时间戳数据类型的新列，并通过以下代码更新该列：

I wanted to integrate them as a single column under timestamp data-type. I have created a new column in timestamp data-type and update the column by following code:

 Update s2
 set dt = year || '-' || month  || '-' || day
               || ' ' || hour  || ':' || min  || ':' || second 

但我遇到以下错误：

ERROR:  column "dt" is of type timestamp without time zone but expression is of type text
LINE 1:  Update temp set dt= year || '-' || month  || '-' || day  ||...
                             ^
HINT:  You will need to rewrite or cast the expression.

********** Error **********

ERROR: column "dt" is of type timestamp without time zone but expression is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 22

此外，我可以通过执行集成varchar 数据类型。

Moreover, I can preform the integration by varchar data-type.

推荐答案

表达式的结果

year || '-' || month  || '-' || day || ' ' || hour  || ':' || min  || ':' || second 

不是时间戳而是纯文本。错误消息只是告诉您，文本类型与 dt 列的类型不合适。

is not a timestamp but a plain text. The error message simply tells you, that the type text is not appropriate to the type of the dt column.

您必须像这样强制转换 complete 表达式：

You must cast the complete expression like this:

(year || '-' || month  || '-' || day || ' ' || hour  || ':' || min  || ':' || second)::timestamp

这篇关于将单独的年，月，日，时，分和秒列集成为单个时间戳列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！