how to convert numeric date and numeric time to datetime column in Postgresql-CodePudding

I am new to postgresql, I have table which have two columns lets say date1 (numeric) and time1 (numeric). I want to convert them in date and time as separate columns and then concatenate them to create new timestamp column using select query.

I have data in date1 column like 44473 and time1 column like 18512121, both are numeric cols.

I have used

select '1900-01-01'::date date1::integer,
       to_timestamp(time1::integer::text,'24HH:MI:SS')::timestamp WITHOUT time zone 
from tablename.

date1        time1
-------------------
2021-10-27   0061-11-04 09:00:00.000

Can any one help me out? I know something is wrong with time1 data.

CodePudding user response：

18512121 does not contain the character : so your format mask for the to_timestamp function shouldn't include that. And your input also contains 8 characters not just 6, so you need a mask that includes that.

to_timestamp(time1::integer::text, 'hh24missms')::time

CodePudding user response：

When we correct the formatting string the time is interpreted correctly.

create table tablename (
date1 int,
time1 int ) ;
insert into tablename values(44473,1851212);

✓

1 rows affected

select 
  '1900-01-01'::date date1::integer "date",
  to_timestamp(time1::integer::text  , 'HH24MISSms')::timestamp WITHOUT time zone "time"
from tablename;

date       | time                    
:--------- | :-----------------------
2021-10-06 | 0001-01-01 18:51:21.2 BC

db<>fiddle here