I am trying to get a timestamp by combining date and time columns in my PostgresQL database and am running into errors. Here is what I have:
+-----------+-----------+
| StartDate | StartTime |
+-----------+-----------+
| 20210301 | 18000000 |
+-----------+-----------+
| 20210302 | 19000000 |
+-----------+-----------+
| 20210303 | 20000000 |
+-----------+-----------+
Here is what I want
+-----------+-----------+-----------------------+
| StartDate | StartTime | StartStamp |
+-----------+-----------+-----------------------+
| 20210301 | 18000000 | 2021-03-01 18:00:00.0 |
+-----------+-----------+-----------------------+
| 20210302 | 19000000 | 2021-03-02 19:00:00.0 |
+-----------+-----------+-----------------------+
| 20210303 | 20000000 | 2021-03-03 20:00:00.0 |
+-----------+-----------+-----------------------+
In MySQL I would be able to get this by using the following sql
SELECT STR_TO_DATE(concat(LPAD("STARTDT", 8, '0'),'-', LEFT(LPAD("STARTTM", 8, '0'),6)),'%Y%m%d-%H%i%s') AS StartStamp
From Table1
To get that in Postgres, I've tried CAST(STARTDT as DATE)
but that resulted in an error. I've tried TO_DATE()
but that also did not work. Is there a way to get the timestamp in PostgreSQL?