Search code examples
sqlprestotrino

How to convert bigint into timestamp in Presto SQL?

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.

Column details

Data type

Solution

  • I tried using Unixtime but it did not workout.

    Because 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:

    _col0
    2022-08-01 03:55:00.000
    2022-08-01 23:55:00.000