Extracting 'time' from a 'timestamp' datatype in Databricks

[TinaN]

We are loading a data source to Databricks that contains columns with 'Time' datatype.  Databricks converts this to 'Timestamp', so I am researching for a way to extract time only. This is what I came up with, but the result isn't quite right.  Is there a way to get a format of double digits (00:00)?

AS SELECT

   CONCAT(extract(hour from timestamp(sun_open)),

   ':',

   (extract(minute from timestamp(sun_open)))),

FROM table...

1) Input from sun_open column format is ,2024-06-01T01:00:00  

2) Result in Databricks is '1:0'

3) But I would like it to be '01:00:00'

Thank you,

Tina

[szymon_dybczak · Accepted solution]

Hi @TinaN ,

I check it in the evening, but try below: 

SELECT date_format(timestamp_column, 'HH:mm:ss') AS time_part
FROM your_table

[TinaN]

Hi Slash - that works perfectly! The results are '01:00:00'. Thank you!

[szymon_dybczak]

No problem, glad that it worked for you 🙂