topic Re: Extracting 'time' from a 'timestamp' datatype in Databricks in Get Started Discussions

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

TinaN — Thu, 18 Jul 2024 15:27:42 GMT

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

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

szymon_dybczak — Thu, 18 Jul 2024 16:28:17 GMT

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

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

TinaN — Thu, 18 Jul 2024 18:01:32 GMT

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

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

szymon_dybczak — Thu, 18 Jul 2024 18:24:04 GMT

No problem, glad that it worked for you 🙂