timefromparts in databricks

Question

Hi Expert,

Sql server query:

create table table12(col varchar(25),col1 nchar(1000))

insert into table12 values('0','620')

select TIMEFROMPARTS (620/100, 620%100,0,0,0) ,620 as col1 from table12

Expected output::

06:20:00 620

How we can get this field in databricks or adf

Answer 1

In Azure Databricks there isn't a direct equivalent to SQL Server TIMEFROMPARTS function. However, you can achieve a similar result by using Spark SQL. Create a DataFrame similar to your table12 and insert the values.

   from pyspark.sql import SparkSession
   from pyspark.sql.functions import col
   # Initialize Spark Session
   spark = SparkSession.builder.appName("Time Conversion").getOrCreate()
   # Create DataFrame
   data = [("0", "620")]
   df = spark.createDataFrame(data, ["col", "col1"])

Then you'll need to extract the hour and minute parts from the col1 field and then format these into a time string.

   from pyspark.sql.functions import format_string
   # Convert and format time
   df_time = df.withColumn("hour", (col("col1").cast("int") / 100).cast("int")) \
               .withColumn("minute", (col("col1").cast("int") % 100).cast("int")) \
               .withColumn("formatted_time", format_string("%02d:%02d:00", "hour", "minute"))

Then, display the result.

 df_time.select("formatted_time", "col1").show()