How to convert bigint into timestamp in Presto SQL?

user_19680444 0
Viewed 101

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

trino presto sql
2 Answers

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:

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

Saw your question tags Presto but it seems you're using Trino (fmr. PrestoSQL). Just wanted to clarify that Trino is a fork of PrestoDB. The main Presto development is PrestoDB and you can download and install the latest here: https://prestodb.io/download.html.

Hope this helps clarify, the major differences are:

  • Only PrestoDB is deployed reliably and in large scale at Meta, Uber, and ByteDance.
  • Only PrestoDB has numerous innovations that aren’t in PrestoSQL: multi-level caching (project RaptorX) to boost query performance by 10X+, table scan improvements (project Aria), disaggregated coordinator (project Fireball) for better reliability, to name a few
  • Only PrestoDB is hosted by Linux Foundation, giving confidence to community users that future releases will remain open. Feel free to join [Presto Slack][1] or use the [Slack direct sign up link][2]
user_137511740
Related