Converting DATE and TIME integers to timestamp

Question

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?

Answer 1

Use to_timestamp() with an appropriate format template.

For string input, with padding to the left like in your example:

to_timestamp(lpad("STARTDT", 8, '0')
          || lpad("STARTTM", 8, '0'), 'YYYYMMDDHH24MISSMS')

For integer input, this should be most efficient:

to_timestamp(lpad(("STARTDT" * bigint '100000000' + "STARTTM")::text, 16, '0') , 'YYYYMMDDHH24MISSMS')

Produces timestamptz (based on your current timezone setting). A plain cast to timestamp gets the according local timestamp:

to_timestamp( ... )::timestamp