0

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?

1
2

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
1
  • Thank you!, That worked – Sidhu177 Apr 1 at 1:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.