How to convert bigint into date and time format and I have two column one is "state change date" and another one "state change time". I have to combine two columns and show in timestamp format. Please suggest a solution here. Thanks in advance.

I tried using Unixtime but it did not workout.

CodePudding user response：

I tried using Unixtime but it did not workout.

Cause your data does not look like unix time, it looks like formatted date time stored in bigint for some reason. You can turn it into varchar and parse correspondingly:

-- sample data
WITH dataset(state_change_date, state_change_time) as (
    VALUES (20220801, 355),
       (20220801, 2355)
)

-- query
SELECT date_parse(cast(state_change_date as varchar) || lpad(cast(state_change_time as varchar), 4 , '0'), '%Y%m%d%k%i')
FROM dataset

Output: