trevor1940
asked on
PostGreSQL: Concatinate Date Time into TimeStamp
Hi
I have a table in PostgreSQL it has (Amongst others) a Date , Time and TimeStamp without time zone columns
What is the syntax for updating the timestamp column from the Date and Time columns?
I have a table in PostgreSQL it has (Amongst others) a Date , Time and TimeStamp without time zone columns
What is the syntax for updating the timestamp column from the Date and Time columns?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Doing that way will probably involve going to text then back again which may be less efficient than using the + operation, try something like this it may be more efficient...
postgres=# create table test2 ( id int, col1 date, col2 time, col3 timestamp );
CREATE TABLE
postgres=# insert into test2(id,col1,col2) values (1, current_date, current_time);
INSERT 0 1
postgres=# select * from test2;
id | col1 | col2 | col3
----+------------+-------- ------+--- ---
1 | 2016-07-23 | 07:43:30.703 |
(1 row)
postgres=# select cast( col1+col2 as timestamp ) from test2;
timestamp
-------------------------
2016-07-23 07:43:30.703
(1 row)
postgres=# select col1+col2 from test2;
?column?
-------------------------
2016-07-23 07:43:30.703
(1 row)
postgres=# create table test2 ( id int, col1 date, col2 time, col3 timestamp );
CREATE TABLE
postgres=# insert into test2(id,col1,col2) values (1, current_date, current_time);
INSERT 0 1
postgres=# select * from test2;
id | col1 | col2 | col3
----+------------+--------
1 | 2016-07-23 | 07:43:30.703 |
(1 row)
postgres=# select cast( col1+col2 as timestamp ) from test2;
timestamp
-------------------------
2016-07-23 07:43:30.703
(1 row)
postgres=# select col1+col2 from test2;
?column?
-------------------------
2016-07-23 07:43:30.703
(1 row)
ASKER