Integrate separate year, month, day, hour, minute and second columns as a single timestamp column

Question

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|
+-------------------+

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 

But I faced by following error:

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

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

Answer 1

You got answers explaining the error: You need to cast the text to timestamp explicitly.

However, the proper solution is to use to_timestamp()

UPDATE s2
SET dt = to_timestamp(year || '-' || month  || '-' || day || ' '
                           || hour  || ':' || min  || ':' || second
                     ,'YYYY-MM-DD hh24:mi:ss');

Why?
The plain cast 'text'::timestamp depends on local settings for date / time format and may work in one installation but "suddenly" fail in another installation of PostgreSQL. The given statement is guaranteed to work, independent of datestyle settings and locale.

Well, to be precise, the pattern in the example ('YYYY-MM-DD hh24:mi:ss') matches ISO 8601 (SQL standard), which is valid for any locale.

Answer 2

You need simple cast from text to timestamp without time zone:

(expression)::timestamp without time zone

For example:

Update s2 set dt = (year || '-' || month  || '-' || day  || ' ' || hour  || ':' || min  || ':' || second)::timestamp without time zone